Last update: 9 Nov 2022
annual report: 2021
10Q: Q3 of 2022
share price data from: 8/28/2022, \$13.72
\(\large{\color {red} {\text{Work in progress notes, 11-18-2022: still working on updates in quarterly data section.}}}\)
\(\large{\color {red} {\text{Concerns about declining equity and ability to fill ships back to 107% level.}}}\)
\(\large{\color {red} {\text{Frank's said he would not dicount to fill, but will acheive historic levels by 2 quarter of 2023.}}}\)
Need to look at impact of future bookings (since this amount is a liability on the balance sheet) to debt ratios.
This notebook was developed to analyze the financial performance of NCLH. The analysis presented primarily uses financial data prior to fiscal year 2019. Most of which is irrelevant now, since NCLH’s consolidated financial sheets are dramatically different following the shock of the pandemic. From a financial perspective, it’s not really possible to compare the finances of post pandemic NCL to the pre pandemic NCL. On account of the large discontinuity in operations, the company’s pre and post pandemic financials need to be considered separately. Click the link here to jump to my analysis of recent quarterly performance.
The years 2020 to present are omitted from the DCF and NAIC analysis since NCLH and the other cruise lines suspended operations in March of 2020 due to the pandemic. My analysis of recent quarterly performance shows that NCLH will run out of cash sometime in 2023 if the company is not able to increase occupancy level close to 100%. Follow the link to the Conclusion.
Norwegian Cruise Line Holdings Ltd., NCLH together with its subsidiaries, operates as a cruise company in North America, Europe, the Asia-Pacific, and internationally. The company operates the Norwegian Cruise Line, Oceania Cruises, and Regent Seven Seas Cruises brands.
Sector(s): Consumer Cyclical Industry: Travel Services
Norwegian Cruise Line Holdings operates a global cruise company with a portfolio of three brands: Norwegian Cruise Line, Oceania Cruises and Regent Seven Seas Cruises. Norwegian commenced operations from Miami in 1966, launching the modern cruise industry by offering weekly departures from Miami to the Caribbean. It is the third-largest cruise line in the world by passengers, controlling about 8.7% of the total worldwide share of the cruise market by passengers as of 2018.
The cruise line was founded as Norwegian Caribbean Line in 1966 by the Norwegian Knut Kloster and the Israeli Ted Arison, with the 8,666-ton, 140-m cruise ship/car ferry, Sunward, which in 1966 operated as a car ferry between Southampton UK and Gibraltar, for that one short season only. Arison soon left to form Carnival Cruise Lines, while Kloster acquired additional ships for Caribbean service. Norwegian pioneered many firsts in the cruise industry, such as the first Out Island Cruise, the first combined air-sea program (marketed as “Cloud 9 Cruises”), which combined low-cost air fares with the cruise, and first shipline to develop new ports in the Caribbean, such as Ocho Rios in Jamaica.
Norwegians’s second and third ships, the Starward and Skyward, were the first newly built ships designed for the cruise line. Like the original Sunward of 1966, they had the capability to carry automobiles through a well-concealed stern door. Later, this area was turned into cabins and a two-deck movie theater, later to be used as a casino. Norwegian was responsible for many of the cruise innovations that have now become standard throughout the industry.
Norwegian acquired Orient Lines in 1998. After talks, Norwegian itself was acquired in 2000 by Star Cruises, a subsidiary of Genting Hong Kong, part of the Malaysia-based Genting Group. In 2007, Star Cruises sold Orient Line’s Marco Polo to Transocean Tours, and Orient Lines ceased operations in early 2008.
In August 2007, Star Cruises sold 50% of Norwegian for \$1 billion to US-based Apollo Management to strengthen Norwegian’s financial position.
In January 2008, the Apollo Funds acquired 50% of the outstanding ordinary share capital of NCLC. As part of this investment, the Apollo Funds assumed control of NCLC’s Board of Directors. Also, in January 2008, the TPG Viking Funds acquired, in the aggregate, 12.5% of NCLC’s outstanding share capital from the Apollo Funds.
In February 2011, NCLH, a Bermuda limited company, was formed.
In January 2013, NCLH completed its IPO and the ordinary shares of NCLC were exchanged for the ordinary shares of NCLH, and NCLH became the owner of 100% of the ordinary shares and parent company of NCLC (the “Corporate Reorganization”). In August 2013 and December 2013, NCLH completed the Secondary Offerings.
In November 2014, NCLH completed the acquisition of Prestige (Oceania and Regent brands). Frank J. Del Rio (founded Oceania in October 2002), became President and Chief Executive Officer of NCLH. Prior to that Kevin M. Sheehan served as the President and Chief Executive Officer of the Company since August 2010.
Segment Reporting
We have concluded that our business has a single reportable segment. Each brand, Oceania, Regent and Norwegian, constitutes a business for which discrete financial information is available and management regularly reviews the operating results and, therefore, each brand is considered an operating segment. Our operating segments have similar economic characteristics, including similar margins and similar products and services; therefore, we aggregate all of the operating segments into one reportable segment.
As of December 31, 2020, NCLH had 28 ships with approximately 59,150 Berths and had orders for nine additional ships to be delivered through 2027. NCLH has nine ships on order across our portfolio of brands. For the Norwegian brand, Project Leonardo will introduce six additional ships with expected delivery dates from 2022 through 2027. For Regent Seven Seas Cruises, NCLH has one Explorer Class Ship on order for delivery in 2023. For Oceania Cruises, NCLH has two Allura Class Ships on order for delivery in 2023 and 2025.
COVID-19
Beginning on March 13, 2020, NCLH suspended all cruise voyages in response to COVID-19. This suspension has been extended through May 31, 2021.
The resumption of operations will be dependent, in part, on NCLH’s ability to comply with various governmental regulations, the severity and duration of the COVID-19 pandemic, the lifting of various travel restrictions and travel bans issued by various countries and communities around the world, as well as port availability. NCLH expects a gradual phased relaunch of our ships after the voyage suspension period, with our ships initially operating at reduced occupancy levels. Our selection of itineraries in the short-term will be predicated by port availability and the safety of the destinations we visit.
Since March 2020, NCLH has launched a series of capital markets transactions to bolster its financial position during the voyage suspension period, which in aggregate raised approximately $5.6 billion.
NCLH has also taken several additional measures to improve their liquidity through deferring certain ship milestone payments, deferring certain debt amortization payments and extending certain maturities under our debt agreements, including under our agreements with export credit agencies (“ECAs”) and related governments. NCLH has also undertaken several proactive cost reduction and cash conservation measures to mitigate the financial and operational impacts of COVID-19, through the reduction of capital expenditures as well as reductions in operating expenses, including ship operating expenses and selling, general and administrative expenses.
On May 5, 2020, in a filing with the Securities and Exchange Commission, Norwegian Cruise Line Holdings (NCLH) said there is “substantial doubt” about its ability to continue as a “going concern” as it faces a liquidity crisis over the next twelve months.
By the next day, NCLH was able to secure over \$2.2 billion of additional liquidity in oversubscribed capital markets transactions, but at a price:
On May 7, 2020, NCLH CEO declared that the company has secured enough liquidity to get through potentially 18 months of zero revenues and may resume cruising later in 2020.
On July 25, 2021, Norwegian Jade set sail from Athens sailing the Greek Isles as the first ship in the Company’s fleet to resume sailing since the global suspension of voyages in March 2020. August 29 Oceania’s Marina sets sail from Stockholm, the first ship of the Oceania fleet.
On May 7 2022 NCL became the first major cruise operator to return the full fleet to service.
}— Document revision history
- 1/10/2022: Copied from VZ notebook and reorganized - Feb 2022: updated quick look, reorganized flow of calculations, corrected usage of financial rates, organized end sections - 23 Mar 2022: Cleaning up financial data spreadsheet. Removed NAIC tab. Removed duplicate revenue data. - 27 Mar 2022: MFG template copied from BMY - 3 May 2022: replaced np.linalg.lstsq with np.polyfit in NAIC forecast, added Future forecast based on historical data notes, Dilution notes, decision model - 6/19/2022: copied from MFG template, old NCLH files given v0 or OLD as filenames. - summer 2022: started looking at quarterly results to determine liquidity and default risk. }—
The following sections of this notebook contain the financial analysis for the company.
New section: Recent quarterly performance
from datetime import datetime, timedelta
from dateutil.relativedelta import relativedelta
from dateutil.parser import parse
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
from dateutil import parser
import os
from pandas.plotting import register_matplotlib_converters
import np_financial
register_matplotlib_converters()This company was selected for analysis because I own 100 shares purchased solely to take part in the shareholder benefit as described below in the dividend section. Also, on occasion, I have large deposits with NCLH for future cruises and in the event of bankruptcy, those deposits may be at risk.
Current news
A review of the financial news sites from yahoo and google showed the following:
- Recent news concerns cruise line debt and return to operations. In June 2022, Carnival provided a 2nd Quarter 2022 business update. The results seemed to be playing in the news as negative for Carnival and by extension the other cruise lines. - Low stock price is in the news. - On March 12, 2020, a class action complaint, Eric Douglas v. Norwegian Cruise Lines, Frank J. Del Rio and Mark A. Kempa, Case No. 1:20-CV-21107, was filed in the United States District Court for the Southern District of Florida, naming the Company, Frank J. Del Rio, the Company’s President and Chief Executive Officer, and Mark A. Kempa, the Company’s Executive Vice President and Chief Financial Officer, as defendants. Subsequently, two similar class action complaints were also filed in the United States District Court for the Southern District of Florida naming the same defendants. - On July 31, 2020, a consolidated amended class action complaint was filed by lead plaintiff’s counsel. The complaint asserts claims, purportedly brought on behalf of a class of shareholders, under Sections 10(b) and 20(a) of the Securities Exchange Act of 1934, and Rule 10b-5 promulgated thereunder, and allege that the Company made false and misleading statements to the market and customers about COVID-19. The complaint seeks unspecified damages and an award of costs and expenses, including reasonable attorneys’ fees, on behalf of a purported class of purchasers of our ordinary shares between February 20, 2020 and March 10, 2020.
Review quarterly results
Quarterly reports dating back to March 31, 2019 are analyzed below. D/E, Book value and liquidity levels indicate NCLH is a distressed company as a result of the pandemic.
Average daily volume
Average daily volume: 21,067,488
Dividend yield
Forward dividend yield: NA
Notes from Crystal Cruises Bankruptcy
Has anyone filed a claim with CFA Travel Insured?
CF Travel Insured has acknowledged our claim and admit that our claim is valid. However, they are taking the position that they won’t pay any claims until they determine how much we will have received from the bankruptcy.
Thank you both. I did file a claim with ABC. I read over the entire policy and it says nothing about waiting for a bankruptcy settlement. They keep saying “we only pay for unreimbursed expenses and won’t know what is unreimbursed until the bankruptcy settlement is complete.”
June 2nd final date for claiming refunds via the assignee
Personally for those who are able to use it, the credit card dispute process is the best course of action - AMEX having proven their worth over and over again over the last 5 months
Those that had a cruise departing from a US port need to submit through the ABC claims process. Your FMC surety bond is among the assets being held and payment for those claims will be made through the assignee.
The ABC claims process is “Assignment for Benefit of Creditors”, a liquidation process under Florida law, that Crystal has undertaken. All foreign flag cruise operators who have cruises leaving from the US (so only the WC would qualify) must post a surety bond of \$32 million with the Federal Maritime Commission. That surety bond, for non-performance, is an asset of Crystal cruises that is part of the ABC liquidation process.
Other than this surety bond, Crystal cruises has very few assets, and other creditors have more priority to those assets, while the surety bond can only be used to pay passengers back.
Crystal Refund Roll Call
Does anyone have info. On Federal Maritime Commission procedure for making a claim…. presumably the FMC will cover up to \$5,000 of non refunded money
The surety bond is an asset being managed by the ABC (Assignment for the Benefit of Creditors) group. You need to file a claim with ABC by 11 June. Make sure you state the departure port (as it has to be US the FMC bond to be valid). FMC stated this in an email to me.
Does anyone have info. On Federal Maritime Commission procedure for making a claim…. presumably the FMC will cover up to \$5,000 of non refunded money
Data from consolidated financial statements and annual reports was collected and entered into a spreadsheet. All numerical data is converted from thousands or millions of dollars to dollars. The stock share price history was obtained from yahoo and is included as a tab in the spreadsheet. Other tabs in the spreadsheet are various worksheets.
ticker = 'NCLH' # company ticker symbol
os.chdir('/home/jeff32/Documents/Dividend Investing/DCF data/')
file_name = ticker+'_Financials.xlsx'
df_dcf_sheet = pd.read_excel(file_name,sheet_name='DCF data')
df_metrics_sheet = pd.read_excel(file_name,sheet_name='metrics')
df_price_history = pd.read_excel(file_name,sheet_name='Historical Prices')
# load quarterly data into dataframe
df_qrt_sheet = pd.read_excel(file_name,sheet_name='QRT data')
# change the working director back to the Jupyter folder
os.chdir('/home/jeff32/Documents/JupyterLab/Discount Cash Flow Analysis/')# convert dates from string to datetime format in stock price history
price_date_list = []
for i in range(len(df_price_history)):
price_date_list.append(datetime.strptime(str(df_price_history['Date'][i]), '%Y-%m-%d'))
df_price_history.insert(0, 'datetime', price_date_list) # insert a new column with datetime data
df_price_history.sort_values(by=['datetime'], inplace=True) # sort data frame by datetime
df_price_history.set_index('datetime',inplace=True)
#df_price_history.head()Generate a new data frame that holds the financial data needed for the DCF model. Data from financial statements is copied into a spreadsheet which contains the data used in the analysis. The data in the DCF_data tab is in a consistent format for ease of use by this notebook. Standard names are used for the rows and columns.
#column names: fiscal years
fy_data = df_dcf_sheet.columns[1:].values.astype('datetime64[Y]')-1970
#line 0: Total revenue
revenue_data = df_dcf_sheet.iloc[0].to_numpy()[1:].astype('float')
#line 1: Cost of goods sold
Cost_of_goods_sold_data = df_dcf_sheet.iloc[1].to_numpy()[1:].astype('float')
#line 2: General and administrative
General_and_administrative_data = df_dcf_sheet.iloc[2].to_numpy()[1:].astype('float')
#line 3: Research and development
Research_and_development_data = df_dcf_sheet.iloc[3].to_numpy()[1:].astype('float')
#line 4: Depreciation and amortization
Depreciation_and_amortization_data = df_dcf_sheet.iloc[4].to_numpy()[1:].astype('float')
#line 5: Investment
Investment_data = df_dcf_sheet.iloc[5].to_numpy()[1:].astype('float')
# line 6: Income before income taxes
Income_before_income_taxes_data = df_dcf_sheet.iloc[6].to_numpy()[1:].astype('float')
# line 7: Income tax
Income_tax_data = df_dcf_sheet.iloc[7].to_numpy()[1:].astype('float')
# line 8: Accounts receivable
Accounts_receivable_data = df_dcf_sheet.iloc[8].to_numpy()[1:].astype('float')
# line 9: Inventories
Inventories_data = df_dcf_sheet.iloc[9].to_numpy()[1:].astype('float')
# line 10: Accounts payable
Accounts_payable_data = df_dcf_sheet.iloc[10].to_numpy()[1:].astype('float')
# line 11: Current assets
Current_assets_data = df_dcf_sheet.iloc[11].to_numpy()[1:].astype('float')
# line 12: Current liabilities
Current_liabilities_data = df_dcf_sheet.iloc[12].to_numpy()[1:].astype('float')
# line 13: Long term debt
Long_term_debt_data = df_dcf_sheet.iloc[13].to_numpy()[1:].astype('float')
# line 14: Shares outstanding
Shares_outstanding_data = df_dcf_sheet.iloc[14].to_numpy()[1:].astype('float')# make a new data frame to store selected financial data
df_dcf_data = pd.DataFrame(data={
'FY':fy_data[::-1],
'revenue':revenue_data[::-1],
'cost_of_goods_sold':Cost_of_goods_sold_data[::-1],
'general_and_administrative':General_and_administrative_data[::-1],
'research_and_development':Research_and_development_data[::-1],
'depreciation':Depreciation_and_amortization_data[::-1],
'investment':Investment_data[::-1],
'income_before_income_taxes':Income_before_income_taxes_data[::-1],
'income_tax':Income_tax_data[::-1],
'accounts_receivable':Accounts_receivable_data[::-1],
'inventories':Inventories_data[::-1],
'accounts_payable':Accounts_payable_data[::-1],
'current_assets':Current_assets_data[::-1],
'current_liabilities':Current_liabilities_data[::-1],
'long_term_debt':Long_term_debt_data[::-1],
'shares_outstanding':Shares_outstanding_data[::-1]
})
#df_dcf_dataDiscounted cash flow (DCF) is a valuation method used to estimate the value of an investment based on its expected future cash flows. DCF analysis attempts to figure out the value of an investment today, based on projections of how much money it will generate in the future. In finance, discounted cash flow (DCF) analysis is a method of valuing a security, project, company, or asset using the concepts of the time value of money. The DCF method used in this notebook follows [1].
The value of any financial investment equals the present value of the expected future cash flows, discounted for risk and timing of these cash flows. The DCF method to value stocks is a four step process.
1. Develop a set of future free cash flows for the corporation based on revenue growth, net operating profit margin, income tax rate and fix and working capital requirements. 2. Estimate the discount rate for the cash flows based on expected timing and risk. 3. Discount the cash flows and total them to calculate the value for the corporation as a whole. 4. Subtract the debt, preferred stock value and other claims and divide by the number of shares outstanding to get the intrinsic value.
Sections - Revenue growth rate - Net operating profit margin - Tax rate - Depreciation Rate - Investment Rate - Working Capital Rate - Current Assets - Current Liabilities - Value of Debt Outstanding - Current stock price - Shares outstanding - 10 year treasury bond yield - Bond yield spread to treasury - Preferred stock yield - Equity risk premium - Company specific beta - DCF model inputs - Future cash flows
The DCF model uses historical financial data to estimate future cash flows. However, future changes are largely unpredictable, so we assume that the past record can be used as a rough guide to the future. The more questionable this assumption is, the less valuable is the analysis. So the DCF model is more useful when applied to stable well established companies, since companies with stable earnings are easier to forecast.
The revenue growth rate (also sometimes called net sales) of the corporation plus any other revenues associated with the main operations of the business. It does not include dividends, interest income or non-operating income. Historic revenue data is obtained from consolidated income statements. The year over year change in revenue is calculated and converted to a percent, then an average revenue growth rate is calculated.
Adjustments
No adjustments for this company.
# calculate the percent change in revenue
pcr = np.zeros(len(df_dcf_data['revenue'].to_numpy())) # percent change in revenue
for i in range(len(df_dcf_data['revenue'].to_numpy()[0:-1])):
pcr[i+1] = ((df_dcf_data['revenue'].to_numpy()[i+1] - df_dcf_data['revenue'].to_numpy()[i])/
df_dcf_data['revenue'].to_numpy()[i+1])*100
width = 100
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Revenue, $B')
# plot revenue as single bar
plt.bar(df_dcf_data['FY'],df_dcf_data['revenue']/1e9, width,color='k')
ax1.tick_params(axis='y')
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:green'
ax2.plot(df_dcf_data['FY'],pcr,'+-g')
ax2.set_ylabel('% Change in revenue',color=color)
ax2.tick_params(axis='y', labelcolor=color)
ax2.set_ylim((0,50))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Revenue')
plt.show()
Observation:
In November 2014, NCLH added Oceania and Regent brands, so 2015 and forward, revenue includes these ships, and Frank J. Del Rio (founded Oceania in October 2002) became President and Chief Executive Officer of NCLH. The acquisition of Oceania and Regent is probably the reason for the spike in % change in revenue in 2015. Afterwards, the revenue growth rate returns to the historic mean.
Clearly seen is the dramatic reduction in revenue in years 2020 and 2021 due to the suspension of operations in March of 2020
Calculate the average revenue growth rate using the years 2016 to 2019.
rgr_avg = pcr[-6:-2].mean()/100 # years 2016 to 2019
print('average revenue growth rate pre Covid: {:.2f}%'.format(rgr_avg*100))average revenue growth rate pre Covid: -94.51%Net Operating Profit should reflect the future revenue generating ability and expense requirements of the operating business that comprise the ongoing operations of the company.
\(\text{NOPM} = \frac{\text{Revenue} - \text{Expenses}}{\text{Revenue}}\)
\(\text{Expenses} = \text{Cost of Goods Sold (CGS)} + \text{General and Administrative (G&A)} + \text{Research and Development (R&D)}\)
General and Administrative (G&A) is also called Sales, General and Administrative (SG&A)
Adjustments
Expenses are the following:
1. Cost of Goods Sold (CGS) - Commissions, transportation and other - Onboard and other - Payroll and related - Fuel - Food - Other 2. Selling, General & Administrative (SGA)
NCLH calls GSA, Marketing, general and administrative. The marketing, general and administrative expense also covers the following items: - Non-cash share-based compensation expenses related to equity awards - Severance payments related to restructuring costs - Expenses related to the redeployment of Norwegian Joy from Asia to the U.S. and the closing of the Shanghai office
The cruise line does not report any R&D expenses. Impairment loss for 2020 was 1,607,797. This has not been included in the spreadsheet as a line item.
Onboard and other revenue primarily consists of revenue from gaming, beverage sales, shore excursions, specialty dining, retail sales, spa services and photo services. Our onboard revenue is derived from onboard activities we perform directly or that are performed by independent concessionaires, from which we receive a share of their revenue.
# NOP = (Revenue - Expenses)
nop = (df_dcf_data['revenue'].to_numpy() - \
(df_dcf_data['cost_of_goods_sold'].to_numpy() + \
df_dcf_data['general_and_administrative'].to_numpy() + \
df_dcf_data['research_and_development'].to_numpy()) )
# net operating profit margin as percent of revenue
nopm = nop/df_dcf_data['revenue'].to_numpy()
# plot as four grouped bar chart with labels on right and working capital rate on left
# calculate position of bars
x1_bar_position = []
x2_bar_position = []
x3_bar_position = []
x4_bar_position = []
for i in df_dcf_data['FY']:
x1_bar_position.append(i-relativedelta(months=3))
x2_bar_position.append(i-relativedelta(months=1))
x3_bar_position.append(i+relativedelta(months=1))
x4_bar_position.append(i+relativedelta(months=3))
width = 40 # the width of the bars
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Net operating profit, \\$B')
ax1.bar(x1_bar_position,df_dcf_data['cost_of_goods_sold'].to_numpy()/1e9, width,label='CGS')
ax1.bar(x2_bar_position,df_dcf_data['general_and_administrative'].to_numpy()/1e9, width,label='G&A')
ax1.bar(x3_bar_position,df_dcf_data['research_and_development'].to_numpy()/1e9, width,label='R&D')
ax1.bar(x4_bar_position,nop/1e9, width,label='NOP')
ax1.tick_params(axis='y')
#ax1.set_ylim((0,4))
ax1.legend()
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:cyan'
ax2.plot(df_dcf_data['FY'],nopm*100,'+-c')
ax2.set_ylabel('% NOPM',color=color)
ax2.tick_params(axis='y', labelcolor=color)
ax2.set_ylim((0,40))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Net Operating Profit')
plt.show()
Observation
Prior to the suspension of operations, the NOPM was above 25%. Calculate the average nopm for pre covid years.
#Average net operating profit margin
nopm_avg = nopm[-6:-2].mean() # years 2016 to 2019
print('average net operating profit margin pre Covid: {:.2f}%'.format(nopm_avg*100))average net operating profit margin pre Covid: -1.00%Tax payments are taken from the consolidated income statement, provision for income taxes. The effect of taxes on profits is accounted for.
\(\text{Tax rate} = \frac{\text{Income taxes}}{\text{Income before income taxes}}\)
Adjustments
In February 2011, NCLH, a Bermuda limited company, was formed. Under current Bermuda law, NCLH is not subject to tax on income and capital gains. NCLH has received from the Minister of Finance under The Exempted Undertakings Tax Protection Act 1966, as amended, an assurance that, in the event that Bermuda enacts legislation imposing tax computed on profits, income, any capital asset, gain or appreciation, or any tax in the nature of estate duty or inheritance, then the imposition of any such tax shall not be applicable to NCLH or to any of our operations or shares, debentures or other obligations, until March 31, 2035.
NCLH and NCLC are tax residents of the U.K. and are subject to normal U.K. corporation tax. During 2013, NCLH implemented a restructuring plan to provide a global tax platform for international expansion. As part of the plan, the Company became a tax resident of the U.K. As such, it qualifies for relief from U.S. Branch Profits taxes under the U.S.-U.K. Tax Treaty. In addition, the restructuring resulted in additional interest and depreciation which reduced the Company’s overall income tax expense.
NCLH under Section 883 of the Code and the related regulations, as a foreign corporation is exempt from U.S. federal income taxation on its U.S. source income derived from the international operation of ships (“shipping income”)
NCLH believes and has taken the position that substantially all of NCLH’s income, including the income of its ship-owning subsidiaries, is properly categorized as shipping income, and that we do not have a material amount of non-qualifying income.
Economic Substance Requirements
NCLH and NCLC are exempted companies formed under the laws of Bermuda and some of their subsidiaries have been formed in Bermuda, Guernsey, Isle of Man, British Virgin Islands, Cayman Islands or the Bahamas. In June 2018, the European Union issued a scoping paper which set out economic substance requirements that targeted international financial centers, including the jurisdictions listed above, were required to adopt before 2019 with regard to relevant entities based in those jurisdictions.
Pursuant to the legislation passed in each jurisdiction, entities subject to each jurisdiction’s laws that carry out relevant activities as specified in such laws, are required to demonstrate substantial economic substance in that jurisdiction. In general terms, substantial economic substance means:
- the entity is actually directed and managed in the jurisdiction;
- core income-generating activities relating to the applicable relevant activity are performed in the jurisdiction; - - there are adequate employees in the jurisdiction;
- the entity maintains adequate physical presence in the jurisdiction; and
- there is adequate operating expenditure in the jurisdiction.
NCLH has evaluated their activities and their subsidiaries and have concluded that in some cases, those activities are ‘relevant activities’ for the purposes of the applicable economic substance laws and that, consequently, certain entities within our organization will be required to demonstrate compliance with these economic substance requirements. NCLH may be subject to increased costs and our management team may be required to devote significant time to satisfying economic substance requirements in certain of these jurisdictions. If such entities cannot establish compliance with these requirements, we may be liable for penalties and fines in the applicable jurisdictions and/or required to re-domicile such entities to different jurisdictions.
# plot as Grouped bar chart with labels on right and tax rate on left
# calculate position of bars
x1_bar_position = []
x2_bar_position = []
for i in df_dcf_data['FY']:
x1_bar_position.append(i-relativedelta(months=1))
x2_bar_position.append(i+relativedelta(months=1))
# calculate tax rate
tax_rate = df_dcf_data['income_tax']/df_dcf_data['income_before_income_taxes']
width = 50 # the width of the bars
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('dollars, \\$M')
rects1 = ax1.bar(x1_bar_position,df_dcf_data['income_before_income_taxes']/1e6, width,
label='Income before income taxes')
rects2 = ax1.bar(x2_bar_position,df_dcf_data['income_tax']/1e6, width,
label='Income taxes')
ax1.tick_params(axis='y')
#ax1.set_ylim((-20,20))
ax1.legend()
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:green'
ax2.plot(df_dcf_data['FY'],tax_rate * 100,'+-g')
ax2.set_ylabel('% Tax rate',color=color)
ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((-40,100))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Tax Rate')
plt.show()
Observation:
NCLH in all but one year, 2019 received an income tax benefit. There is a lot of information in the annual and quarterly reports on NCLH’s tax situation. The acquisition of Prestige in 2014 included deferred tax assets. Certain foreign corporations are exempt from U. S. federal income or branch profits tax on U.S.-source income derived from or incidental to the international operation of ships.
Calculate average tax rate for pre covid years.
# Average tax rate
tax_rate_avg = tax_rate[-6:-2].mean()
print('average tax rate: {:.2f}%'.format(tax_rate_avg*100))average tax rate: -0.13%The depreciation rate is used to project the future net investment cash flows. The effect is to reduce the amount of FCFF. Depreciation amounts are from the Consolidated Statement of Cash Flows, Depreciation and Amortization.
\(\text{Depreciation Rate}=\frac{\text{Depreciation and Amortization}}{\text{Revenues}}\)
Depreciation is the write off or expensing of a percentage of the historical cost of an asset over the asset’s useful life. Property, plant and equipment (PP&E) are long term or non current assets owned or controlled by the company and used to manufacture and or sell the company’s products. The balance sheet typically shows all categories of PP&E grouped together, net of accumulated depreciation. Depreciation represents wear and tear on an asset or the fact that an asset gets used up over time. Companies record depreciation expense in the income statement every year for all depreciable assets in service or used by the company during the year. The difference between GAAP and Tax Accounting methods is handled through deferred taxes.
Amortization is the write off or expensing of the cost of a financial instrument or an intangible asset over the shorter of its useful life or legal life. Amortization is similar to depreciation and reflects the declining useful life and value of the intangible asset over time. Companies in research and development intensive fields typically have many patents. Such industries include high technology, pharmaceuticals and chemicals.
# depreciation rate
depreciation_rate = df_dcf_data['depreciation'] / df_dcf_data['revenue'].to_numpy()
# plot depreciation on left and rate on right
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('dollars, $B')
ax1.bar(df_dcf_data['FY'],df_dcf_data['depreciation']/1e9, width=100,color='k')
ax1.tick_params(axis='y')
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:Blue'
ax2.plot(df_dcf_data['FY'],depreciation_rate*100,'+-')
ax2.set_ylabel('% Depreciation rate',color=color)
ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((0,30))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Depreciation')
plt.show()
Observation:
Depreciation of ships is computed on a straight-line basis over the weighted average useful lives of primarily 30 years after a 15% reduction for the estimated residual value of the ship.
Ship improvement costs that NCLH believes add value to our ships are capitalized to the ship and depreciated over the shorter of the improvements’ estimated useful lives or the remaining useful life of the ship. When they record the retirement of a ship component included within the ship’s cost basis, they estimate the net book value of the component being retired and remove it from the ship’s cost basis.
Repairs and maintenance activities are charged to expense as incurred. We account for Dry-dock costs under the direct expense method which requires us to expense all Dry-dock costs as incurred.
NCLH determines the weighted average useful lives of their ships based primarily on our estimates of the useful lives of the ships’ major component systems on the date of acquisition, such as cabins, main diesels, main electric, superstructure and hull. The useful lives of ship improvements are estimated based on the economic lives of the new components. In addition, to determine the useful lives of the ship or ship components, we consider the impact of the historical useful lives of similar assets, manufacturer recommended lives and anticipated changes in technological conditions.
Calculate average depreciation rate for pre-covid years.
# average depreciation rate
depreciation_rate_avg = depreciation_rate[-6:-2].mean()
print('average depreciation rate: {:.2f}%'.format(depreciation_rate_avg*100))average depreciation rate: 21.20%Taken from Consolidated Statement of Cash Flows, Cash used for investing activities. Net investment in the dollar amount needed to support the growth of the firm. Included investments in properties, plant equipment in excess of the depreciation expenses associated with past investments. Net investment decreases the amount of money available to the stockholders. Investment in property, plant and equipment is necessary to both maintain service and sales and also to grow revenues and profits. Investment amounts should include capital expenditures and research and development.
\(Ir=\frac {\text {Capital Expenditures}}{\text{Revenues}}\)
For this company, the yearly investment amounts are taken from the Consolidated Statements of Cash Flows, Net Cash Used in Investing Activities.
Adjustments
See 2020 annual report pdf page 125 for Ship Construction Contracts
2018 annual report:
Sophisticated and efficient maintenance and operations systems support the technical superiority and modern look of our fleet. In addition to routine repairs and maintenance performed on an ongoing basis and in accordance with applicable requirements, each of our ships is generally taken out of service, approximately every 24 to 60 months, for a period of one or more weeks for scheduled maintenance work, repairs and improvements performed in Dry-dock.
Dry-dock interval is a statutory requirement controlled under IMO requirements reflected in chapters of the International Convention of the Safety of Life at Seas (“SOLAS”) and to some extent the International Load Lines Convention. Under these regulations, it is required that a passenger ship Dry-dock once in five years (depending on age of vessel), twice in 5 years (depending on flag state and age of vessel) and the maximum interval between each Dry-dock cannot exceed 3 years (depending age of vessel and flag state).
However, most of our international ships qualify under a special exemption provided by the Bahamas and/or Marshall Islands (flag state), as applicable, after meeting certain criteria set forth by the ship’s flag state to Dry-dock once every 5 years.
Future capital commitments consist of contracted commitments, including ship construction contracts, and future expected capital expenditures necessary for operations as well as our ship refurbishment projects. As of December 31, 2018, anticipated capital expenditures were \$1.6 billion, \$1.2 billion and \$0.7 billion for the years ending December 31,2019, 2020 and 2021, respectively. We have export credit financing in place for the anticipated expenditures related to ship construction contracts of \$0.6 billion, \$0.5 billion and \$0.2 billion for the years ending December 31, 2019, 2020 and 2021, respectively. These future expected capital expenditures will significantly increase our depreciation and amortization expense as we take delivery of the ships.
Project Leonardo will introduce an additional six ships, each approximately 140,000 Gross Tons with approximately 3,300 Berths, with expected delivery dates from 2022 through 2027, subject to certain conditions. We have a Breakaway Plus Class Ship, Norwegian Encore, with approximately 168,000 Gross Tons with 4,000 Berths, on order for delivery in the fall of 2019. For the Regent brand, we have orders for two Explorer Class Ships, Seven Seas Splendor and an additional ship, to be delivered in 2020 and 2023, respectively. Each of the Explorer Class Ships will be approximately 55,000 Gross Tons and 750 Berths. For the Oceania Cruises brand, we have orders for two Allura Class Ships to be delivered in 2022 and 2025. Each of the Allura Class Ships will be approximately 67,000 Gross Tons and 1,200 Berths.
The combined contract prices of the 11 ships on order for delivery was approximately €7.9 billion, or \$9.1 billion based on the euro/U.S. dollar exchange rate as of December 31, 2018. We have obtained export credit financing which is expected to fund approximately 80% of the contract price of each ship, subject to certain conditions. We do not anticipate any contractual breaches or cancellations to occur. However, if any such events were to occur, it could result in, among other things, the forfeiture of prior deposits or payments made by us and potential claims and impairment losses which may materially impact our business, financial condition and results of operations.
Capitalized interest for the years ended December 31, 2018, 2017 and 2016 was \$30.4 million, \$29.0 million and \$33.7 million, respectively, primarily associated with the construction of our newbuild ships.
See 2018 annual report pdf page 48 for Contractual Obligations,
See 2018 annual report pdf page 80 for Property and Equipment, Net, includes anounts for
- Ships - Ships improvements - Ships under construction - Land and land improvements - Other
Ships under construction include progress payments to the shipyard, planning and design fees and other associated costs. Capitalized interest costs which were primarily associated with the construction or revitalization of ships amounted to \$30.4 million, \$29.0 million and \$33.7 million for the years ended December 31, 2018, 2017 and 2016, respectively.
See 2018 annual report pdf page 93 for Ship Construction Contracts, for minimum annual payments for non-cancelable ship construction contracts.
# investment rate
investment_rate = df_dcf_data['investment'] / df_dcf_data['revenue'].to_numpy()
# plot investment on left and rate on right
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('dollars, \\$B')
ax1.bar(df_dcf_data['FY'],df_dcf_data['investment']/1e9, width=100,color='k')
ax1.tick_params(axis='y')
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:Blue'
ax2.plot(df_dcf_data['FY'],investment_rate*100,'+-')
ax2.set_ylabel('% New Investment Rate',color=color)
ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((-10,40))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('New Investment')
plt.show()
Observation:
Capital expenditures for new ships is NCLH’s largest investment expense. NCLH pays for this expense by financing activities, where, typically 80% of the construction cost is financed. Looking at the years 2016 to 2019, investments are increasing at a rate of about 25% of revenue.
Calculate average investment rate for pre-covid years.
# average investment rate
investment_rate_avg = investment_rate[-6:-2].mean()
print('average investment rate: {:.2f}%'.format(investment_rate_avg*100))average investment rate: 38.26%Working capital is needed to support the corporate sales effort of any company. Often a company’s incremental change in net working capital either positive or negative is approximately proportional to its change in revenue.
\(\text{Working capital} = \text{Accounts Receivable} + \text{Inventories} - \text{Accounts Payable}\)
Working capital is a company’s net investment in its accounts receivable and its inventories (cash outflows), minus its accounts payable (a cash inflow). Working capital and taxes are cash outflows from the corporation that are not available to pay debts and stockholders.
Adjustments
No adjustments for this company.
# plot as four grouped bar chart with labels on right and working capital rate on left
# calculate position of bars
x1_bar_position = []
x2_bar_position = []
x3_bar_position = []
x4_bar_position = []
for i in df_dcf_data['FY']:
x1_bar_position.append(i-relativedelta(months=3))
x2_bar_position.append(i-relativedelta(months=1))
x3_bar_position.append(i+relativedelta(months=1))
x4_bar_position.append(i+relativedelta(months=3))
# calculate working capital rate
working_capital = (df_dcf_data['accounts_receivable'] + df_dcf_data['inventories']) - \
df_dcf_data['accounts_payable']
working_capital_rate = working_capital / df_dcf_data['revenue']
width = 40 # the width of the bars
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('dollars, \\$B')
rects1 = ax1.bar(x1_bar_position,df_dcf_data['accounts_receivable']/1e9, width,\
label='Accounts Receivable')
rects2 = ax1.bar(x2_bar_position,df_dcf_data['inventories']/1e9, width, label='Inventory')
rects2 = ax1.bar(x3_bar_position,df_dcf_data['accounts_payable']/1e9, width, label='Accounts Payable')
rects2 = ax1.bar(x4_bar_position,working_capital/1e9, width, label='Working Capital')
ax1.tick_params(axis='y')
#ax1.set_ylim((-50,200))
ax1.legend()
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:Blue'
ax2.plot(df_dcf_data['FY'],working_capital_rate * 100,'+-')
ax2.set_ylabel('% Working Capital Rate',color=color)
ax2.tick_params(axis='y', labelcolor=color)
ax2.set_ylim((0,50))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Working Capital')
plt.show()
Observation:
The working capital amounts for some years is negative because the accounts payable is much higher. A review of the 2018 didn’t provide a clear indication for the large accounts payable amount.
The average working capital rate is calculated for the pre-covid years of 2016 to 2019.
# average working capital rate
working_capital_rate_avg = working_capital_rate[-6:-2].mean()
print('average working capital rate: {:.2f}%'.format(working_capital_rate_avg*100))average working capital rate: 0.94%Total Current Assets from the most recent balance sheet statement of the company. Current assets include inventory, cash and accounts receivables.
Adjustments
None for this company.
# plot Short Term Assets
width = 100 # the width of the bars
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
plt.bar(df_dcf_data['FY'],df_dcf_data['current_assets']/1e9, width)
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Current assets')
plt.ylabel('dollars, \\$B')
plt.grid()
plt.show()
Observation:
In 2020, NCLH increased cash on hand to pay for pandemic pause in activities.
Use 2019 data for current assets.
sta = df_dcf_data['current_assets'].iloc[-3]
print('current assets: ${:.2f}B'.format(sta/1e9))current assets: $3.56BTotal Current Liabilities from the most recent balance sheet consolidated statement.
Adjustments
None for this company.
# plot Short Term Liabilities
width = 100 # the width of the bars
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
plt.bar(df_dcf_data['FY'],df_dcf_data['current_liabilities']/1e9, width)
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Current liabilities')
plt.ylabel('dollars, \\$B')
plt.grid()
plt.show()
Observation:
During the pandemic pause, NCLH has decreased their short term liabilities by scaling back operations in 2020. Then with the return to service the current liabilities increased to the 2019 level.
Use average of 2016 to 2019 data for current liabilities.
stl = df_dcf_data['current_liabilities'].iloc[6:-2].mean()
print('Average of current liabilities pre Covid: ${:.2f}B'.format(stl/1e9))Average of current liabilities pre Covid: $2.88BAmount of debt outstanding from the most recent balance sheet of the company.
Adjustments
None for this company.
# calculate the percent change in debt, pcd
pcd = np.zeros(len(df_dcf_data['long_term_debt'].to_numpy())) # percent change in debt
for i in range(len(df_dcf_data['long_term_debt'].to_numpy()[0:-1])):
pcd[i+1] = ((df_dcf_data['long_term_debt'].to_numpy()[i+1] - df_dcf_data['long_term_debt'].to_numpy()[i])/
df_dcf_data['long_term_debt'].to_numpy()[i+1])*100
width = 100
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Dollars, $B')
# plot revenue as single bar
plt.bar(df_dcf_data['FY'],df_dcf_data['long_term_debt']/1e9, width,color='k')
ax1.tick_params(axis='y')
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:green'
ax2.plot(df_dcf_data['FY'],pcd,'+-g')
ax2.set_ylabel('% Change in debt',color=color)
ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((-40,100))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('debt')
plt.show()
Observation:
NCLH has increased long term debt on account of Covid-19 in order to pay for activities during the pause.
Use average for years 2016 to 2019 data for long term debt.
vod = df_dcf_data['long_term_debt'].iloc[6:-2].mean()
print('average total long term debt and other pre Covid: ${:.2f}B'.format(vod/1e9))average total long term debt and other pre Covid: $8.27BMost recent stock price for the company. The current stock price is used to calculate the market value of the firm. Use the market value when looking at market capitalization for common stock.
csp = 13.72 # current stock price
print('current stock price: ${:,.2f}'.format(csp))current stock price: $13.72The number of shares outstanding is used to calculate the intrinsic stock value.
Use 2019 data for shares outstanding.
so = df_dcf_data['shares_outstanding'].iloc[-3] # shares outstanding
print('shares outstanding, basic: {:,.0f}'.format(so))shares outstanding, basic: 254,728,932# calculate the percent change in shares outstanding, pcso
pcso = np.zeros(len(df_dcf_data['shares_outstanding'].to_numpy())) # percent change in debt
for i in range(len(df_dcf_data['shares_outstanding'].to_numpy()[0:-1])):
pcso[i+1] = ((df_dcf_data['shares_outstanding'].to_numpy()[i+1] - df_dcf_data['shares_outstanding'].to_numpy()[i])/
df_dcf_data['shares_outstanding'].to_numpy()[i+1])*100
width = 100
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('shares outstanding, M')
# plot revenue as single bar
plt.bar(df_dcf_data['FY'],df_dcf_data['shares_outstanding']/1e6, width,color='k')
ax1.tick_params(axis='y')
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:green'
ax2.plot(df_dcf_data['FY'],pcso,'+-g')
ax2.set_ylabel('% Change in shares outstanding',color=color)
ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((-5,25))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Shares outstanding')
plt.show()
print('average shares outstanding growth rate: {:.2f}%'.format(pcso[1:].mean()))
print('shares outstanding pre-Covid: {:,.0f}'.format(df_dcf_data['shares_outstanding'].iloc[-3]))average shares outstanding growth rate: 7.67%
shares outstanding pre-Covid: 254,728,932Observation:
The number of shares outstanding comes from the 2020 annual report, see page 50 and Consolidated Statements, pdf page 87. This number needs to be researched a bit more to and should include any dilution from convertible bonds. As of 2020 the basic shares were 254,728,932 and the diluted shares were 254,728,932.
Dilution occurs when a company issues new shares that result in a decrease in existing stockholders’ ownership percentage of that company. Stock dilution can also occur when holders of stock options, such as company employees, or holders of other optionable securities exercise their options. When the number of shares outstanding increases, each existing stockholder owns a smaller, or diluted, percentage of the company, making each share less valuable.
Investigate why there is a historic growth trend in the number of shares outstanding. Search annual report for dilutive actions: - share sales - convertible debt - employee options
Search results:
Recent common share issuance has been the most dilutive, almost doubling the number of shares outstanding. For example, in March 2021, NCLH completed an equity offering that resulted in 52,577,947 ordinary shares being issued for gross proceeds of $1.6 billion.
A relatively smaller number of shares were granted for employee share based compensation, for example, in March 2022, NCLH granted 4.8 million time-based restricted share unit awards to our employees, which primarily vest in substantially equal installments over three years. Additionally, in March 2022, NCLH granted 1.9 million performance-based restricted share units to certain members of our management team, which vest upon the achievement of certain pre-established performance targets established through 2024 and the satisfaction of an additional time-based vesting requirement that generally requires continued employment through March 1, 2025.
June 16, 2022 Form 8K: Among other things, the Restated 2013 Plan reflects amendments to: i. increase the number of the Company’s ordinary shares that may be delivered pursuant to all awards granted under the Restated 2013 Plan by an additional 7,000,000 shares, from 32,375,106 shares to a new maximum aggregate limit of 39,375,106 shares;
print('value ordinary shares authorized for awards: ${:,.2f}B'.format(39375106*csp/1e9))value ordinary shares authorized for awards: $0.54BThe 10 year treasury yield is used as a measure of the risk free rate.
Yield: 3.0350%
iShares 7-10 Year Treasury Bond ETF (IEF)
Average Yield to Maturity: 3.04%
tby = (3.035+3.04)/2/100 # 10 year treasury bond yield, average of data from sources listed above
print('10 year treasury bond yield: {:,.2f}%'.format(tby*100))10 year treasury bond yield: 3.04%The spread to treasury implies that all corporate debt will have a higher yield than yields associated with comparable maturity US Treasury Bonds. The best way to determine default risk is to see how a particular company’s debt is trading in the market and compare it on a spread basis with comparable maturity yields.
Look at the following or use a default rating system that is published by the three major rating agencies, Standards and Poors Corp, Moody’s Investor Services and Fitch & Company.
PIMCO Active Bond Exchange-Traded Fund (BOND)
Yield: 2.95%
iShares 5-10 Year Investment Grade Corporate Bond ETF (IGIB)
Average Yield to Maturity: 4.74%
iShares 10+ Year Investment Grade Corporate Bond ETF (IGLB)
Average Yield to Maturity: 5.08%
Web resources: - http://www.standardpoor.com/
- http://bond.yahoo.com/rates.html
- http://www.moodys.com/cust/default.asp
- http://www.fitchibca.com/corporate/index.cfm
bystt = ((2.95+4.74+5.08)/3-tby)/100 # bond yield spread (average) to treasury spread
print('Bond yield spread to treasury: {:,.2f}%'.format(bystt*100))Bond yield spread to treasury: 4.23%Amount of preferred stock outstanding from the most recent balance sheet of the company.
psy = 0/100 # preferred stock yield
print('preferred stock yield: {:,.2f}%'.format(psy*100))
vps = 0 # value of preferred stock
print('value of preferred stock: {:,.2f}'.format(vps))preferred stock yield: 0.00%
value of preferred stock: 0.00The Beta used is Beta of Equity. Beta is the monthly price change of a particular company relative to the monthly price change of the S&P 500. The time period for Beta is 5 years when available. This value can be obtained at yahoo finance.
A measure of risk of an individual stock. It measures volatility of return - a higher beta means a higher risk. A financial model that uses Beta as its sole measure of risk (signal factor model) is called a Capital Asset Pricing Model (CAPM).
beta = 2.47 # company specific beta
print('Company specific beta: {:,.2f}'.format(beta))Company specific beta: 2.47Below are the DCF model inputs. These values were calculated above.
# various rates
rgr = rgr_avg # revenue growth rate
print('revenue growth rate: {:,.2f}%'.format(rgr*100))
nopm = nopm_avg # net operating profit margin
print('net operating profit margin: {:,.2f}%'.format(nopm*100))
tr = tax_rate_avg # tax rate
print('tax rate: {:,.2f}%'.format(tr*100))
dr = depreciation_rate_avg # depreciation rate (% of revenue)
print('depreciation rate: {:,.2f}%'.format(dr*100))
ir = investment_rate_avg # investment rate (% of revenue)
print('investment rate: {:,.2f}%'.format(ir*100))
wcr = working_capital_rate_avg # working capital rate (% of revenue)
print('working capital rate: {:,.2f}%'.format(wcr*100))revenue growth rate: -94.51%
net operating profit margin: -1.00%
tax rate: -0.13%
depreciation rate: 21.20%
investment rate: 38.26%
working capital rate: 0.94%Excess return period
The excess return period is based on a judgment call. The authors of [1] use the 1-5-7-10 rule. They group companies into one of four general categories and excess return periods. They use a 10 year excess return period to calculate what they would consider the maximum value. They use a more conservative 1 year, 5 year or 7 year return period to calculate a more reasonable or minimum value.
- 1 year: Boring companies that operate in a highly competitive, low margin industry in which they have nothing particular going for them. - 5 year: Decent companies that have a recognizable name and decent reputation and perhaps a regulatory benefit (utility company), but can’t control pricing or growth. - 7 year: Good companies with good brand names, large companies of scale, good marketing channels and consumer identification (e.g. McDonald’s) - 10 year: Great companies with great growth potential, tremendous marketing power, band names and in-place benefits (e.g. Intel, Microsoft, Coca Cola, Disney)
The excess return period used for the base case is ten years, which should lead to a higher calculated intrinsic value.
Use 2019 for starting revenue amount.
# General Inputs
fy_start = df_dcf_data['FY'].iloc[-1].year # fiscal year to start excess return period
erp = 10 # excess return period, years
rev_start = df_dcf_data['revenue'].to_numpy()[-3] # starting revenues for excess return period
print('starting year: {:.0f}'.format(fy_start))
print('excess return period: {:.0f} years'.format(erp))
print('starting revenues: ${:,.2f}B'.format(rev_start/1e9))
print('shares outstanding: {:,.0f}'.format(so))starting year: 2022
excess return period: 10 years
starting revenues: $1.28B
shares outstanding: 254,728,932ps_mv = vps # preferred stock, market value
print('preferred stock, market value : ${:,.2f}B'.format(ps_mv/1e9))
cs_mv = csp*so # common stock, market value
print('common stock, market value: ${:,.2f}B'.format(cs_mv/1e9))preferred stock, market value : $0.00B
common stock, market value: $3.49BLong Term Debt, Market Value, ltd_mv
Use the book value for long term debt. Various online resources can be used to research this item. These include, Bondsonline and Bloomberg. The book value of debt and preferred stock is an accounting measure that relates to how much money was raised by the company when each security was issued. The market value of debt and the preferred and common stock is the price that specific obligations would trade at in today’s market.
Long term debt for firms can take one of two forms. It can be a long-term loan from a bank or other financial institution or it can be a long-term bond issued to financial markets, in which case the creditors are the investors in the bond. Firms often have long term obligations that are not captured in the long term debt item. These include obligations to lessors on assets that firms have leased, to employees in the form of pension fund and health care benefits yet to be paid, and to the government in the form of taxes deferred. In the last two decades, accountants have increasingly moved towards quantifying these liabilities and showing them as long term liabilities.
ltd_mv = vod # market value of long term debt
tmv = ltd_mv+ps_mv+cs_mv # total market value
print('total market value: ${:,.2f}B'.format(tmv/1e9))total market value: $11.76BCost of Common Equity, cce
The expected excess return a hypothetical average investor would require of a diversified portfolio of stock (assumed beta = 1.0) over the yield on the 10-year Treasury Bond. The annual rate of return that an investor expects to earn when investing in shares of a company is known as the cost of common equity. It includes dividends and increases in the market value.
cce = tby+beta*eq_rp # cost of common equity or the expected return for the stock
print('cost of common equity: {:,.2f}%'.format(cce*100))cost of common equity: 10.45%Long Term Debt, Average Yield, ltd_ay
The total cost of long term debt.
ltd_ay = tby+bystt # long term debt average yield
print('long term debt average yield: {:,.2f}%'.format(ltd_ay*100))long term debt average yield: 7.26%Long Term Debt, After Tax Yield, ltd_aty
The tax benefits of long term debt. Interest payments are tax deductible for the company.
ltd_aty = ltd_ay*(1-tr) # long term debt after tax yield
print('long term debt after tax yield: {:,.2f}%'.format(ltd_aty*100))
ltd_pc = vod/tmv # weight for long term debt
ltd_ate = ltd_aty*ltd_pc # after tax effect of long term debt
ps_ay = psy # preferred stock, average yield
ps_aty = ps_ay # preferred stock, average yield
print('preferred stock, average yield: {:,.2f}%'.format(ps_aty*100))
ps_pc = ps_mv/tmv # preferred stock, % capital
ps_ate = ps_aty*ps_pc # preferred stock, after tax effect
cs_ay = cce # common stock, average yield
cs_aty = cce # common stock, after tax yield
print('common stock, after tax yield: {:,.2f}%'.format(cs_aty*100))
cs_pc = cs_mv/tmv # common stock, % capital
cs_ate = cs_aty*cs_pc # common stock, after tax effect
print('common stock, after tax effet: {:,.2f}%'.format(cs_ate*100))
tate = ltd_ate+ps_ate+cs_ate # total after tax effect
print('total after tax effect: {:,.2f}%'.format(tate*100))
tpc = ltd_pc+ps_pc+cs_pc # total % Capital
print('total % Capital: {:,.2f}%'.format(tpc*100))long term debt after tax yield: 7.27%
preferred stock, average yield: 0.00%
common stock, after tax yield: 10.45%
common stock, after tax effet: 3.10%
total after tax effect: 8.22%
total % Capital: 100.00%Weighted average cost of capital
A company’s weighted average cost of capital (WACC) is the weighted average of the company’s current cost of debt and equity calculated by using current debt, preferred stock and common stock market values. The WACC of the company, calculated after tax, is the discount rate used in the DCF valuation procedures. The WACC, which is the cost of the different components of financing used by the firm, weighted by their market value proportions. These include debt, preferred stock, and common stock.
WACC: Weighted Average Cost of Capital, the rate used to discount cash flows, based on the following three factors. 1. Base rate of return. 2. Expected return based on debt and preferred stock. 3. Expected return on common stock and Beta.
All adjusted for the tax advantage of interest payments and the percentage of debt, preferred stock and common stock.
wacc = tate
print('weighted average cost of capital: {:.1f}%'.format(wacc*100))weighted average cost of capital: 8.2%The future cash flows to the firm are projected based on revenue growth. The cash flows are then discounted using the WACC and the ISV is calculated.
# make a list of the fiscal years in excess return period
fy = np.zeros(erp+1)
fy[0] = fy_start
for i in range(1,erp+1):
fy[i]=fy_start+i
rev = np.zeros(len(fy))
ciwc = np.zeros(len(fy))
rev[0] = rev_start #*rgr+rev_start # find the future revenue using constant revenue growth rate
for i in range(1,len(fy)):
rev[i] = rev[i-1]*rgr+rev[i-1] # find the future revenue
ciwc[i] = (rev[i]-rev[i-1])*wcr # find the change in working capital
net_op = np.zeros(len(fy)) # net operating profit
adj_taxes = np.zeros(len(fy))
nopat = np.zeros(len(fy))
invest = np.zeros(len(fy))
depre = np.zeros(len(fy))
net_invest = np.zeros(len(fy))
fcff = np.zeros(len(fy))
disc_fact = np.zeros(len(fy))
disc_fcff = np.zeros(len(fy))
# calculate values in table
for i in range(1,len(fy)):
net_op[i] = rev[i]*nopm # net operating profit margin
adj_taxes[i] = net_op[i]*tr # net operating profit adjusted for taxes
nopat[i] = net_op[i]-adj_taxes[i] # after tax net operating profit
invest[i] = rev[i]*ir # future investments
depre[i] = rev[i]*dr # future depreciations
net_invest[i] = invest[i]-depre[i] # net investments
fcff[i] = nopat[i]-net_invest[i]-ciwc[i] # free cash flow to the firm
disc_fact[i] = 1/((1+wacc)**i) # discount factor
disc_fcff[i] = disc_fact[i]*fcff[i] # discounted free cash flow to the firm
dcrv = nopat[-1]/wacc*disc_fact[-1] # discounted corporate residual value
derp_fcff = disc_fcff.sum() # discounted excess return period FCFF
tcv = derp_fcff+dcrv+sta # total corporate value
tvce = tcv-vod-vps-stl # total value of common equity
isv = tvce/so # intrinsic stock value
# print cash flows in a table
print('{:4s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}'.format('Year','Rev','NOP','AdjTaxes',
'NOPAT','Invest.','Deprec.','dInvest.','dWC','FCFF','DF','DF*FCFF'))
for i in range(len(fy)):
print('{:4.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.4f}{:10,.0f}'.format(fy[i],
rev[i]/1e6,net_op[i]/1e6,adj_taxes[i]/1e6,nopat[i]/1e6,invest[i]/1e6,depre[i]/1e6,(invest[i]-depre[i])/1e6,ciwc[i]/1e6,
fcff[i]/1e6,disc_fact[i],disc_fcff[i]/1e6))Year Rev NOP AdjTaxes NOPAT Invest. Deprec. dInvest. dWC FCFF DF DF*FCFF
2022 1,280 0 0 0 0 0 0 0 0 0.0000 0
2023 70 -1 0 -1 27 15 12 -11 -1 0.9241 -1
2024 4 -0 0 -0 1 1 1 -1 -0 0.8539 -0
2025 0 -0 0 -0 0 0 0 -0 -0 0.7891 -0
2026 0 -0 0 -0 0 0 0 -0 -0 0.7292 -0
2027 0 -0 0 -0 0 0 0 -0 -0 0.6738 -0
2028 0 -0 0 -0 0 0 0 -0 -0 0.6227 -0
2029 0 -0 0 -0 0 0 0 -0 -0 0.5754 -0
2030 0 -0 0 -0 0 0 0 -0 -0 0.5317 -0
2031 0 -0 0 -0 0 0 0 -0 -0 0.4913 -0
2032 0 -0 0 -0 0 0 0 -0 -0 0.4540 -0# Intrinsic Value
print('discounted excess return period FCFF: ${:,.2f}B'.format(derp_fcff/1e9))
print('discounted corporate residual value: ${:,.2f}B'.format(dcrv/1e9))
print('total corporate value: ${:,.2f}B'.format(tcv/1e9))
print('total value of common equity: ${:,.2f}B'.format(tvce/1e9))
tvce_baseline = tvce # save value as baseline case
isv_baseline = isv # save the isv for the baseline case
print('intrinsic stock value, baseline case: ${:,.2f}'.format(isv_baseline))
print('current stock price: ${:,.2f}'.format(csp))discounted excess return period FCFF: $-0.00B
discounted corporate residual value: $-0.00B
total corporate value: $3.56B
total value of common equity: $-7.60B
intrinsic stock value, baseline case: $-29.82
current stock price: $13.72Observation:
The base line DCF analysis produces an intrinsic stock value of \$80.
The following print statements format the inputs to the model similar to how they are presented on the Valuepro page.
print('{:>35s} {:<10.0f} {:>35s} {:,.3f}'.format('Excess return period, years:',erp,'Depreciation rate, %:',dr*100))
print('{:>35s} {:<10,.2f} {:>35s} {:,.3f}'.format('Starting revenues, $B:',
rev_start/1e9,'Investment rate, %:',ir*100))
print('{:>35s} {:<10,.3f} {:>35s} {:,.3f}'.format('Revenue growth rate, %:',
rgr*100,'Working capital rate, %:',wcr*100))
print('{:>35s} {:<10,.3f} {:>35s} {:,.3f}'.format('Net operating profit margin, %:',
nopm*100,'Current assets, $B:',sta/1e9))
print('{:>35s} {:<10,.3f} {:>35s} {:.3f}'.format('Tax rate, %:',
tr*100,'Current liabilities, $B:',stl/1e9))
print('{:>35s} {:<10,.2f} {:>35s} {:,.2f}'.format('Current stock price, $:',
csp,'Equity risk premium, %:',eq_rp*100))
print('{:>35s} {:<10,.0f} {:>35s} {:,.2f}'.format('Shares outstanding, basic, M:',
so/1e6,'Company specific beta:',beta))
print('{:>35s} {:<10,.2f} {:>35s} {:.3f}'.format('10 year treasury bond yield, %:',
tby*100,'Total long term debt and other, $B:',vod/1e9))
print('{:>35s} {:<10,.2f} {:>35s} {:,.3f}'.format('Bond yield spread to treasury, %:',
bystt*100,'Value of preferred stock, $B:',vps/1e9))
print('{:>35s} {:<10,.2f}'.format('Preferred stock yield, %:',psy*100)) Excess return period, years: 10 Depreciation rate, %: 21.200
Starting revenues, $B: 1.28 Investment rate, %: 38.264
Revenue growth rate, %: -94.514 Working capital rate, %: 0.935
Net operating profit margin, %: -1.000 Current assets, $B: 3.558
Tax rate, %: -0.126 Current liabilities, $B: 2.883
Current stock price, $: 13.72 Equity risk premium, %: 3.00
Shares outstanding, basic, M: 255 Company specific beta: 2.47
10 year treasury bond yield, %: 3.04 Total long term debt and other, $B: 8.269
Bond yield spread to treasury, %: 4.23 Value of preferred stock, $B: 0.000
Preferred stock yield, %: 0.00 # weighted average cost of capital inputs
print('Weighted Average Cost of Capital')
print('Cost of common equity')
print('{:s}'.format('-'*37))
print('{:>32s} {:,.2f}'.format('10 year treasury bond yield, %:',tby*100))
print('{:>32s} {:,.2f}'.format('Company specific beta:',beta))
print('{:>32s} {:,.2f}'.format('Equity risk premium, %:',eq_rp*100))
print('{:s}'.format('-'*37))
print('{:>32s} {:,.2f}'.format('Cost of common equity, %:',cce*100))
print()
print('Market Capitalization and After-Tax Weighted Average Cost of Capital')
print()
print('{:s}{:^10s}{:^10s}{:^10s}{:^15s}{:^15s}'.format(' '*20,'Current','After-Tax','Market','%','Weighted After-'))
print('{:s}{:^10s}{:^10s}{:^10s}{:^15s}{:^15s}'.format(' '*20,'Yield','Yield','Value','Capitalization','Tax Yield'))
print('{:s}'.format('-'*80))
print('{:<15s}{:>12.2f}{:>10.2f}{:>10,.0f}{:>12.2f}{:>15.2f}'.format('Long term debt',
ltd_ay*100,(tby+eq_rp)*(1-tr)*100,vod/1e9,ltd_pc*100,ltd_ate*100))
print('{:<15s}{:>12.2f}{:>10.2f}{:>10,.0f}{:>12.2f}{:>15.2f}'.format('Preferred stock',
psy*100,ps_ate*100,vps/1e9,ps_pc*100,ps_ate*100))
print('{:<15s}{:>12.2f}{:>10.2f}{:>10,.0f}{:>12.2f}{:>15.2f}'.format('Common stock',
cs_ay*100,cs_aty*100,cs_mv/1e9,cs_pc*100,cs_aty*100))
print('{:s}'.format('-'*80))
print('{:<37s}{:>10,.0f}{:>12.2f}{:>15.2f}'.format('',tmv/1e9,tpc*100,wacc*100))Weighted Average Cost of Capital
Cost of common equity
-------------------------------------
10 year treasury bond yield, %: 3.04
Company specific beta: 2.47
Equity risk premium, %: 3.00
-------------------------------------
Cost of common equity, %: 10.45
Market Capitalization and After-Tax Weighted Average Cost of Capital
Current After-Tax Market % Weighted After-
Yield Yield Value Capitalization Tax Yield
--------------------------------------------------------------------------------
Long term debt 7.26 6.05 8 70.29 5.11
Preferred stock 0.00 0.00 0 0.00 0.00
Common stock 10.45 10.45 3 29.71 10.45
--------------------------------------------------------------------------------
12 100.00 8.22The following adjustments were made to various model parameters. - excess return period was adjusted to a more conservative 5 years - revenue growth rate was adjusted to 9% (base case = 9.428%) - net operating profit margin was adjusted to 25% (base case = 28.593%) - tax rate was adjusted to 1% (base case = -0.486%) - depreciation rate was adjusted to 10% (base case = 9.397%) - investment rate was adjust to 25% (base case = 25.003%) - working capital rate was set to an even 1% (base case = 0.979%) - weighted average cost of capital was adjusted up by 2% to reflect higher interest rates and provide a margin of safety (base case = 3.8%)
print('adjusted DCF input values and rates')
erp = 5
print('excess return period: {:,.0f} years'.format(erp))
rgr = 9/100
print('revenue growth rate: {:,.1f}%'.format(rgr*100))
nopm = isv_s1_nopm = 25/100 # save nopm rate for NAIC preferred method
print('net operating profit margin: {:.2f}%'.format(nopm*100))
tr = isv_s1_tr = 1/100 # # save tax rate for NAIC preferred method
print('tax rate: {:.2f}%'.format(tr*100))
dr = 10/100
print('depreciation rate: {:,.2f}%'.format(dr*100))
ir = 25/100 # investment rate (% of revenue)
print('investment rate: {:,.2f}%'.format(ir*100))
wcr = 1/100
print('working capital rate: {:,.1f}%'.format(wcr*100))
wacc_adj = (wacc+0.02) # weighted average cost of capital, increased by 2%
print('weighted average cost of capital: {:.1f}%'.format(wacc_adj*100))adjusted DCF input values and rates
excess return period: 5 years
revenue growth rate: 9.0%
net operating profit margin: 25.00%
tax rate: 1.00%
depreciation rate: 10.00%
investment rate: 25.00%
working capital rate: 1.0%
weighted average cost of capital: 10.2%# make a list of the fiscal years in excess return period
fy = np.zeros(erp+1)
fy[0] = fy_start
for i in range(1,erp+1):
fy[i]=fy_start+i
rev = np.zeros(len(fy))
ciwc = np.zeros(len(fy))
rev[0] = rev_start #*rgr+rev_start # find the future revenue using constant revenue growth rate
for i in range(1,len(fy)):
rev[i] = rev[i-1]*rgr+rev[i-1] # find the future revenue
ciwc[i] = (rev[i]-rev[i-1])*wcr # find the change in working capital
net_op = np.zeros(len(fy))
adj_taxes = np.zeros(len(fy))
nopat = np.zeros(len(fy))
invest = np.zeros(len(fy))
depre = np.zeros(len(fy))
net_invest = np.zeros(len(fy))
fcff = np.zeros(len(fy))
disc_fact = np.zeros(len(fy))
disc_fcff = np.zeros(len(fy))
# calculate values in table
for i in range(1,len(fy)):
net_op[i] = rev[i]*nopm # net operating profit
adj_taxes[i] = net_op[i]*tr # net operating profit adjusted for taxes
nopat[i] = net_op[i]-adj_taxes[i] # after tax net operating profit
invest[i] = rev[i]*ir # future investments
depre[i] = rev[i]*dr # future depreciations
net_invest[i] = invest[i]-depre[i] # net investments
fcff[i] = nopat[i]-net_invest[i]-ciwc[i] # free cash flow to the firm
disc_fact[i] = 1/((1+wacc_adj)**i) # discount factor
disc_fcff[i] = disc_fact[i]*fcff[i] # discounted free cash flow to the firm
dcrv = nopat[-1]/wacc*disc_fact[-1] # discounted corporate residual value
derp_fcff = disc_fcff.sum() # discounted excess return period FCFF
tcv = derp_fcff+dcrv+sta # total corporate value
tvce = tcv-vod-vps-stl # total value of common equity
isv = tvce/so # intrinsic stock value
# print cash flows in a table
print('{:4s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}'.format(
'Year','Rev','NOP','AdjTaxes',
'NOPAT','Invest.','Deprec.','dInvest.','dWC','FCFF','DF','DF*FCFF'))
for i in range(len(fy)):
print('{:4.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.4f}{:10,.0f}'.
format(fy[i],rev[i]/1e6,net_op[i]/1e6,adj_taxes[i]/1e6,nopat[i]/1e6,invest[i]/1e6,depre[i]/1e6,
(invest[i]-depre[i])/1e6,ciwc[i]/1e6,fcff[i]/1e6,disc_fact[i],disc_fcff[i]/1e6))Year Rev NOP AdjTaxes NOPAT Invest. Deprec. dInvest. dWC FCFF DF DF*FCFF
2022 1,280 0 0 0 0 0 0 0 0 0.0000 0
2023 1,395 349 3 345 349 140 209 1 135 0.9073 122
2024 1,521 380 4 376 380 152 228 1 147 0.8232 121
2025 1,658 414 4 410 414 166 249 1 160 0.7469 120
2026 1,807 452 5 447 452 181 271 1 175 0.6777 118
2027 1,969 492 5 487 492 197 295 2 190 0.6149 117# Intrinsic Value
print('discounted excess return period FCFF: ${:,.2f}B'.format(derp_fcff/1e9))
print('discounted corporate residual value: ${:,.2f}B'.format(dcrv/1e9))
print('total corporate value: ${:,.2f}B'.format(tcv/1e9))
print('total value of common equity: ${:,.2f}B'.format(tvce/1e9))
tvce_S1 = tvce # save value as scenario 1
isv_S1 = isv # save the isv for scenario 1 case
print('intrinsic stock value, scenario 1 case: ${:,.2f}'.format(isv_S1))
print('current stock price: ${:,.2f}'.format(csp))discounted excess return period FCFF: $0.60B
discounted corporate residual value: $3.65B
total corporate value: $7.80B
total value of common equity: $-3.35B
intrinsic stock value, scenario 1 case: $-13.15
current stock price: $13.72The DCF model calculates with adjustments an intrinsic stock value of \$75
Run the DCF model again with current debt and current number of shares out standing.
vod = 12563518*1000 #df_dcf_data['long_term_debt'].iloc[-1]
print('Total long term debt and other: ${:.2f}B'.format(vod/1e9))
#print('Total long term debt and other pre-Covid: ${:.2f}B'.format(df_dcf_data['long_term_debt'].iloc[-3]/1e9))Total long term debt and other: $12.56Bltd_mv = vod # market value of long term debt
tmv = ltd_mv+ps_mv+cs_mv # total market value
print('total market value: ${:,.2f}B'.format(tmv/1e9))total market value: $16.06Bcce = tby+beta*eq_rp # cost of common equity or the expected return for the stock
print('cost of common equity: {:,.2f}%'.format(cce*100))cost of common equity: 10.45%ltd_ay = tby+bystt # long term debt average yield
print('long term debt average yield: {:,.2f}%'.format(ltd_ay*100))long term debt average yield: 7.26%ltd_aty = ltd_ay*(1-tr) # long term debt after tax yield
print('long term debt after tax yield: {:,.2f}%'.format(ltd_aty*100))
ltd_pc = vod/tmv # weight for long term debt
ltd_ate = ltd_aty*ltd_pc # after tax effect of long term debt
ps_ay = psy # preferred stock, average yield
ps_aty = ps_ay # preferred stock, average yield
print('preferred stock, average yield: {:,.2f}%'.format(ps_aty*100))
ps_pc = ps_mv/tmv # preferred stock, % capital
ps_ate = ps_aty*ps_pc # preferred stock, after tax effect
cs_ay = cce # common stock, average yield
cs_aty = cce # common stock, after tax yield
print('common stock, after tax yield: {:,.2f}%'.format(cs_aty*100))
cs_pc = cs_mv/tmv # common stock, % capital
cs_ate = cs_aty*cs_pc # common stock, after tax effect
print('common stock, after tax effet: {:,.2f}%'.format(cs_ate*100))
tate = ltd_ate+ps_ate+cs_ate # total after tax effect
print('total after tax effect: {:,.2f}%'.format(tate*100))
tpc = ltd_pc+ps_pc+cs_pc # total % Capital
print('total % Capital: {:,.2f}%'.format(tpc*100))long term debt after tax yield: 7.19%
preferred stock, average yield: 0.00%
common stock, after tax yield: 10.45%
common stock, after tax effet: 2.27%
total after tax effect: 7.90%
total % Capital: 100.00%wacc = tate
print('weighted average cost of capital: {:.1f}%'.format(wacc*100))weighted average cost of capital: 7.9%so = 417734591 # df_dcf_data['shares_outstanding'].iloc[-1] # shares outstanding
print('shares outstanding, basic: {:,.0f}'.format(so))shares outstanding, basic: 417,734,591print('adjusted DCF input values and rates')
erp = 5
print('excess return period: {:,.0f} years'.format(erp))
rgr = 9/100
print('revenue growth rate: {:,.1f}%'.format(rgr*100))
nopm = isv_s1_nopm = 25/100 # save nopm rate for NAIC preferred method
print('net operating profit margin: {:.2f}%'.format(nopm*100))
tr = isv_s1_tr = 1/100 # # save tax rate for NAIC preferred method
print('tax rate: {:.2f}%'.format(tr*100))
dr = 10/100
print('depreciation rate: {:,.2f}%'.format(dr*100))
ir = 25/100 # investment rate (% of revenue)
print('investment rate: {:,.2f}%'.format(ir*100))
wcr = 1/100
print('working capital rate: {:,.1f}%'.format(wcr*100))
wacc_adj = (wacc+0.02) # weighted average cost of capital, increased by 2%
print('weighted average cost of capital: {:.1f}%'.format(wacc_adj*100))adjusted DCF input values and rates
excess return period: 5 years
revenue growth rate: 9.0%
net operating profit margin: 25.00%
tax rate: 1.00%
depreciation rate: 10.00%
investment rate: 25.00%
working capital rate: 1.0%
weighted average cost of capital: 9.9%# make a list of the fiscal years in excess return period
fy = np.zeros(erp+1)
fy[0] = fy_start
for i in range(1,erp+1):
fy[i]=fy_start+i
rev = np.zeros(len(fy))
ciwc = np.zeros(len(fy))
rev[0] = rev_start #*rgr+rev_start # find the future revenue using constant revenue growth rate
for i in range(1,len(fy)):
rev[i] = rev[i-1]*rgr+rev[i-1] # find the future revenue
ciwc[i] = (rev[i]-rev[i-1])*wcr # find the change in working capital
net_op = np.zeros(len(fy))
adj_taxes = np.zeros(len(fy))
nopat = np.zeros(len(fy))
invest = np.zeros(len(fy))
depre = np.zeros(len(fy))
net_invest = np.zeros(len(fy))
fcff = np.zeros(len(fy))
disc_fact = np.zeros(len(fy))
disc_fcff = np.zeros(len(fy))
# calculate values in table
for i in range(1,len(fy)):
net_op[i] = rev[i]*nopm # net operating profit
adj_taxes[i] = net_op[i]*tr # net operating profit adjusted for taxes
nopat[i] = net_op[i]-adj_taxes[i] # after tax net operating profit
invest[i] = rev[i]*ir # future investments
depre[i] = rev[i]*dr # future depreciations
net_invest[i] = invest[i]-depre[i] # net investments
fcff[i] = nopat[i]-net_invest[i]-ciwc[i] # free cash flow to the firm
disc_fact[i] = 1/((1+wacc_adj)**i) # discount factor
disc_fcff[i] = disc_fact[i]*fcff[i] # discounted free cash flow to the firm
dcrv = nopat[-1]/wacc*disc_fact[-1] # discounted corporate residual value
derp_fcff = disc_fcff.sum() # discounted excess return period FCFF
tcv = derp_fcff+dcrv+sta # total corporate value
tvce = tcv-vod-vps-stl # total value of common equity
isv = tvce/so # intrinsic stock value
# print cash flows in a table
print('{:4s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}'.format(
'Year','Rev','NOP','AdjTaxes',
'NOPAT','Invest.','Deprec.','dInvest.','dWC','FCFF','DF','DF*FCFF'))
for i in range(len(fy)):
print('{:4.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.0f}{:10,.4f}{:10,.0f}'.
format(fy[i],rev[i]/1e6,net_op[i]/1e6,adj_taxes[i]/1e6,nopat[i]/1e6,invest[i]/1e6,depre[i]/1e6,
(invest[i]-depre[i])/1e6,ciwc[i]/1e6,fcff[i]/1e6,disc_fact[i],disc_fcff[i]/1e6))Year Rev NOP AdjTaxes NOPAT Invest. Deprec. dInvest. dWC FCFF DF DF*FCFF
2022 1,280 0 0 0 0 0 0 0 0 0.0000 0
2023 1,395 349 3 345 349 140 209 1 135 0.9099 123
2024 1,521 380 4 376 380 152 228 1 147 0.8280 122
2025 1,658 414 4 410 414 166 249 1 160 0.7534 121
2026 1,807 452 5 447 452 181 271 1 175 0.6855 120
2027 1,969 492 5 487 492 197 295 2 190 0.6238 119# Intrinsic Value
print('discounted excess return period FCFF: ${:,.2f}B'.format(derp_fcff/1e9))
print('discounted corporate residual value: ${:,.2f}B'.format(dcrv/1e9))
print('total corporate value: ${:,.2f}B'.format(tcv/1e9))
print('total value of common equity: ${:,.2f}B'.format(tvce/1e9))
tvce_S1 = tvce # save value as scenario 1
isv_S1 = isv # save the isv for scenario 1 case
print('intrinsic stock value, scenario 2 case: ${:,.2f}'.format(isv_S1))
print('current stock price: ${:,.2f}'.format(csp))discounted excess return period FCFF: $0.60B
discounted corporate residual value: $3.85B
total corporate value: $8.01B
total value of common equity: $-7.44B
intrinsic stock value, scenario 2 case: $-17.80
current stock price: $13.72In scenario 2, the CDF model, values NCLH at 22 per share.
This analysis follows the NAIC stock selection guide (SSG) [2]. The SSG relates revenue growth, EPS and share price history and makes a prediction about the future share price.
The National Association of Investors Clubs (NAIC) is a nonprofit organization dedicated to educating individual investors and investment clubs to become successful lifelong investors. NAIC’s Stock Selection Guide (SSG) is used in the following cells to analyze the company’s growth and whether the stock is selling at a reasonable price.
The SSG was originally developed in the 1950s as a paper worksheet by the not-for-profit National Association of Investors Corporation (NAIC). The SSG aims to aid individual investors in the fundamental analysis and selection of common stocks by reviewing components of a company’s growth, quality, and value.
The NAIC analysis presented below was performed for years 2019 and prior, which are the pre-covid years. Companies with negative earnings are difficult to evaluate with this method.
# column names: fiscal years
fy_data = df_metrics_sheet.columns[1:].values.astype('datetime64[Y]')-1970
# line 0: Net income
net_income_data = df_metrics_sheet.iloc[0].to_numpy()[1:].astype('float')
# line 1: Shareholder equity
shareholder_equity_data = df_metrics_sheet.iloc[1].to_numpy()[1:].astype('float')
# line 2: Total liabilities
total_liabilities_data = df_metrics_sheet.iloc[2].to_numpy()[1:].astype('float')
# line 3: Free cash flow, Net cash provided by operating activities
free_cash_flow_data = df_metrics_sheet.iloc[3].to_numpy()[1:].astype('float')
# line 4: Dividends
dividends_data = df_metrics_sheet.iloc[4].to_numpy()[1:].astype('float')
# line 5: Total assets
total_assets_data = df_metrics_sheet.iloc[5].to_numpy()[1:].astype('float')
# line 6: Earnings per share
eps_data = df_metrics_sheet.iloc[6].to_numpy()[1:].astype('float')
# line 7: Dividends per share
dps_data = df_metrics_sheet.iloc[7].to_numpy()[1:].astype('float')
# line 8: Total tangible assets
total_tangible_assets_data = df_metrics_sheet.iloc[8].to_numpy()[1:].astype('float')
# line 9: Liabilities w/o deposits
liabilities_wo_deposits_data = df_metrics_sheet.iloc[9].to_numpy()[1:].astype('float')
# line 10: Provision for credit losses
provision_for_credit_losses_data = df_metrics_sheet.iloc[10].to_numpy()[1:].astype('float')
# line 11: Short-term borrowings
short_term_borrowings_data = df_metrics_sheet.iloc[11].to_numpy()[1:].astype('float')
# line 12: Preferred stock
preferred_stock_data = df_metrics_sheet.iloc[12].to_numpy()[1:].astype('float')
# line 13: Net cash used in investing activities
net_cash_used_in_investing_activities_data = df_metrics_sheet.iloc[13].to_numpy()[1:].astype('float')# make a new data frame to store data from metrics sheet
df_metrics_data = pd.DataFrame(data={
'FY':fy_data[::-1],
'net_income':net_income_data[::-1],
'shareholder_equity':shareholder_equity_data[::-1],
'total_liabilities':total_liabilities_data[::-1],
'free_cash_flow':free_cash_flow_data[::-1],
'dividends':dividends_data[::-1],
'total_assets':total_assets_data[::-1],
'eps':eps_data[::-1],
'dps':dps_data[::-1],
'total_tangible_assets':total_tangible_assets_data[::-1],
'liabilities_wo_deposits':liabilities_wo_deposits_data[::-1],
'provision_for_credit_losses':provision_for_credit_losses_data[::-1],
'short_term_borrowings':short_term_borrowings_data[::-1],
'preferred_stock':preferred_stock_data[::-1],
'net_cash_used_in_investing_activities':net_cash_used_in_investing_activities_data[::-1]
})
#df_metrics_datacheck for matching years in both data frames
if all(df_dcf_data['FY'] == df_metrics_data['FY']) != True:
print('error, years in data frame don\'t match')
stop # this is not python code, so jupyterlab will throw an error
else:
print('OK, years in data frame match')OK, years in data frame matchHigh and low price history for each year
From the daily price history obtained from yahoo finance, the high and low closing price for each is obtained and the data saved to the financial data frame as new columns.
#column names: fiscal years
years_list = df_metrics_sheet.columns[1:].values.astype('str')[::-1]
# convert years to datetime format
year_ended_list = []
for i in years_list:
year_ended_list.append(datetime.strptime(i, '%Y'))
# make emnpy lists to store open, close, high and low price data for each fiscal year
fy_open = []
fy_close = []
fy_high = []
fy_low = []
for i in year_ended_list:
start = i
end = i + relativedelta(years=1)
p1 = df_price_history.truncate(before=start, after=end)
if len(p1) == 0:
fy_open.append(np.nan)
fy_close.append(np.nan)
fy_high.append(np.nan)
fy_low.append(np.nan)
else:
fy_open.append(p1['Open'].iloc[0])
fy_close.append(p1['Close'].iloc[-1])
fy_high.append(p1['Close'].max())
fy_low.append(p1['Close'].min())
# convert from list to numpy array
fy_open = np.asarray(fy_open)
fy_close = np.asarray(fy_close)
fy_high = np.asarray(fy_high)
fy_low = np.asarray(fy_low)Plotting the data
The annual sales, EPS and the high and low share price is plotted on a semilog plot. A consistent percentage change in the data will plot on the semi-log chart as a straight line.
The stock price is plotted separately from the sales and earnings for clarity.
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
# figsize() function to adjust the size
plt.subplots(1,2,figsize=(15, 5))
# using subplot function and creating
# plot one
plt.subplot(1, 2, 1)
width = 3 # the width of the bars
#plt.bar(year_ended_list,fy_high-fy_low, width,bottom=fy_low,label='price')
j = 0
for i in year_ended_list:
color = 'green'
if fy_open[j] > fy_close[j]: color= 'red'
# high/low lines
plt.plot([i,i],[fy_low[j],fy_high[j]],color=color, linewidth=width)
# open marker
plt.plot([i,i-relativedelta(months=1)], [fy_open[j],fy_open[j]], color=color, linewidth=width)
# close marker
plt.plot([i,i+relativedelta(months=1)], [fy_close[j],fy_close[j]], color=color, linewidth=width)
j += 1
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.ylim((0,70))
plt.title('Yearly stock high and low price range')
plt.ylabel('stock price, $')
#plt.legend()
plt.grid()
# using subplot function and creating plot two
plt.subplot(1, 2, 2)
plt.plot(df_metrics_data['FY'],df_dcf_data['revenue']/1e9,'+-',label='revenue, $B')
plt.plot(df_metrics_data['FY'],df_metrics_data['eps'],'+-',label='EPS, $')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
#plt.yscale('log')
#plt.yticks([0.1,1,10,100,1000,10000],['0.1','1','10','100','1000','10000'])
#plt.ylim((0.1,1000))
plt.title('Revenue and EPS')
plt.ylabel('Revenue and EPS')
plt.legend()
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
Observation:
Share price has been trading in the 400 to 60 dollar range the years 2015 to 2019, indicating that the market is not impressed with the company. Average EPS (ignoring 2020) has been increasing along with revenues for the years 2015 to 2019.
Section 3 of the SSG is the Price-Earnings history. The following table is built from the high and low prices each year and the earnings per share. The high and low Price/Earnings ratios are calculated for each year and are listed in the columns labeled h-per and l-per.
print('{:4s}{:>10s}{:>10s}{:>10s}{:>10s}{:>10s}'.format('year','high','low','eps',
'h-per','l-per'))
for i in range(len(year_ended_list)):
print('{:s}{:10,.2f}{:10,.2f}{:10,.2f}{:10,.2f}{:10,.2f}'.format(year_ended_list[i].strftime("%Y"),
fy_high[i], fy_low[i],df_metrics_data['eps'][i],
fy_high[i]/df_metrics_data['eps'][i],
fy_low[i]/df_metrics_data['eps'][i]))year high low eps h-per l-per
2012 nan nan 0.95 nan nan
2013 35.47 24.79 0.50 70.94 49.58
2014 48.03 29.65 1.64 29.29 18.08
2015 63.76 42.93 1.89 33.74 22.71
2016 57.99 34.40 2.79 20.78 12.33
2017 59.46 42.79 3.33 17.86 12.85
2018 60.93 39.55 4.28 14.24 9.24
2019 59.56 40.71 4.33 13.76 9.40
2020 59.65 7.77 -15.75 -3.79 -0.49
2021 33.71 17.79 -12.33 -2.73 -1.44
2022 23.72 10.38 -5.41 -4.38 -1.92Average high and P/E for select years
The average price to earning ratio based on high and low stock prices is calculated.
Use data from 2014 to 2019.
#Average high P/E for years
pe_avg_high = (fy_high/df_metrics_data['eps'])[1:-2].mean()
print('average high P/E {:.2f}'.format(pe_avg_high))
#Average low P/E for years
pe_avg_low = (fy_low/df_metrics_data['eps'])[2:-2].mean()
print('average low P/E {:.2f}'.format(pe_avg_low))average high P/E 24.60
average low P/E 12.02Use polyfit to get EPS slope and intercept of a least square fit.
Use data from 2013 to 2019.
y = df_metrics_data['eps'][:-2]
x = np.arange(len(y))
m, c = np.polyfit(x, y, 1)
print('EPS slope: {:.2f}'.format(m))
print('EPS intercept: {:.2f}'.format(c))
lstsq_fit = m*x + c # data points for each yearEPS slope: -0.81
EPS intercept: 3.68# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('EPS')
ax1.plot(df_metrics_data['FY'],df_metrics_data['eps'], 'o',label='EPS')
ax1.plot(df_metrics_data['FY'][:-2],lstsq_fit, '-',label='least squares fit')
ax1.tick_params(axis='y')
#ax1.set_ylim((0,4))
ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('EPS and least squares fit')
plt.show()
Using the equation for the best fit line, find the y value for the eps point at five years in the future.
# estimated eps in 5 years
eps_5yr_est = m*(x[-1]+5) + c
print('estimated eps in 5 years: {:.1f}'.format(eps_5yr_est))estimated eps in 5 years: -6.8Using the high and low price to earning ratio from above and the projected eps, calculate the range of stock price in five years.
naic_price_eps_low = eps_5yr_est*pe_avg_low
naic_price_eps_high = eps_5yr_est*pe_avg_high
print('estimated price range in 5 years: ${:.2f} to ${:.2f}'.format(naic_price_eps_low,naic_price_eps_high))estimated price range in 5 years: $-82.30 to $-168.48This is the estimated price range of the stock based on projected EPS and is a guide for what the stock price might be if conditions remain the same. Since the slope of the EPS history is negative, the projected stock price is negative.
See page 87 and figure 10-1, Need the following data:
- estimate sales in 5 years based on sales growth - NOPM - Tax rate - shares outstanding
Net Operating Profit should reflect the future revenue generating ability and expense requirements of the operating business that comprise the ongoing operations of the company.
\(\text{NOPM} = \frac{\text{Revenue} - \text{Expenses}}{\text{Revenue}}\)
Tax payments are taken from the consolidated income statement, provision for income taxes. The effect of taxes on profits is accounted for.
\(\text{Tax rate} = \frac{\text{Income taxes}}{\text{Income before income taxes}}\)
To get future EPS
\(\text{future EPS} = \frac {\text{future revenue} \times \text{NOPM} \times \text{(1-tax rate)}}{\text{number of shares}}\)
Use polyfit to get revenue least square fit
Use data from 2013 to 2019.
y = df_dcf_data['revenue'][:-2]/1e6
x = np.arange(len(y))
m, c = np.polyfit(x, y, 1)
print('revenue slope: {:.2f}'.format(m))
print('revenue intercept: {:.2f}'.format(c))
lstsq_fit = m*x + c # data points for each yearrevenue slope: 243.34
revenue intercept: 3069.45# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('dollars, $M')
ax1.plot(df_metrics_data['FY'],df_dcf_data['revenue']/1e6, 'o',label='revenue')
ax1.plot(df_metrics_data['FY'][:-2],lstsq_fit, '-',label='least squares fit')
ax1.tick_params(axis='y')
#ax1.set_ylim((0,4))
ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Revenue and least squares fit')
plt.show()
Using the equation for the best fit line, find the y value for the EPS point at five years in the future.
# estimated revenue in 5 years
rev_5yr_est = m*(x[-1]+5) + c
print('estimated rev in 5 years: ${:,.1f}M'.format(rev_5yr_est))estimated rev in 5 years: $6,232.9Mneed to include estimate of number of shares outstanding in 5 years
print('starting revenues: ${:,.2f}'.format(rev_start/1e9))starting revenues: $1.28Using the adjusted NOPM and tax rate from scenario 1.
pm_nopm = isv_s1_nopm # use nopm from scenario 1
pm_tax_rate = isv_s1_tr # use tr from scenario 1
pm_eps_5yr_est = rev_5yr_est*pm_nopm*(1-pm_tax_rate)*1e6/df_dcf_data['shares_outstanding'].iloc[-1]
#pm_eps_5yr_est = rev_5yr_est*nopm_avg*1e6/df_dcf_data['shares_outstanding'].iloc[-1]
print('using preferred method: estimated eps in 5 years: ${:.2f}'.format(pm_eps_5yr_est))using preferred method: estimated eps in 5 years: $3.66Using the high and low price to earning ratio from above and the projected EPS, calculate the range of stock price in five years.
naic_price_pm_low = pm_eps_5yr_est*pe_avg_low
naic_price_pm_high = pm_eps_5yr_est*pe_avg_high
print('estimated price range in 5 years from preferred method: {:.2f} to {:.2f}'.format(
naic_price_pm_low,naic_price_pm_high))estimated price range in 5 years from preferred method: 43.94 to 89.94Observation: Based on revenue growth, the projected stock price is a bit higher than the current price. However, based on price history, the stock is not expected to appreciate.
The projected future stock price is estimated from the results shown in this notebook based on DCF intrinsic stock value, the NAIC method or a combination of both. The DCF method does not consider market sentiment or popularity of the stock, whereas the NAIC method looks at the PE and EPS to develop the historical consensus that the market has put on the price of the stock. Both the NAIC and the DCF valuation should be considered. The DCF valuation is of the current ISV which is used as an indication of the future value, since it is assumed that the market price will converge eventually to the intrinsic value.
The estimated future stock price considers the following:
- base case ISV - Senario ISV - NAIC EPS growth - NAIC preferred method
Using 5 year NAIC as a conservative estimate for the 10 year value and the analysis results, a judgment call is made concerning the price to put on the future value of the stock.
print('estimated price range in 5 years from EPS: ${:.2f} to ${:.2f}'.format(naic_price_eps_low,naic_price_eps_high))
print('estimated price range in 5 years from preferred method: ${:.2f} to ${:.2f}'.format(
naic_price_pm_low,naic_price_pm_high))
print('intrinsic stock value, baseline case: ${:,.2f}'.format(isv_baseline))
print('intrinsic stock value, scenario 1 case: ${:,.2f}'.format(isv_S1))
print('current stock price: ${:,.2f}'.format(csp))estimated price range in 5 years from EPS: $-82.30 to $-168.48
estimated price range in 5 years from preferred method: $43.94 to $89.94
intrinsic stock value, baseline case: $-29.82
intrinsic stock value, scenario 1 case: $-17.80
current stock price: $13.72The estimated price range in 5 years from the preferred method is \$86.14 to \$116.85. However, this no longer applies as a result of the pandemic.
The estimated price range in 5 years from the preferred method is \$86.14 to \$116.85. Taking the average and using that value on the IRR calculations.
Using the average of:
- low estimated price from EPS and the low - estimated price from the preferred method - intrinsic stock value, scenario 1 case
use average of NAIC low price
#fsp = (naic_price_eps_low + naic_price_pm_low + csp)/3 # estimated future stock price
fsp = (naic_price_eps_low) # estimated future stock price
print('estimated future stock price: ${:,.2f}'.format(fsp))estimated future stock price: $-82.30NCLH does not pay a dividend.
NCLH provides a shareholder’s benefit which provides \$100 of onboard credit for a 7 night cruise and \$250 onboard credit per stateroom on sailings of 15 days or more.
I purchased 100 shares on Feb 24, 2020 at \$42.9050 per share. My total cost basis is \$4,290.50 and so far I’ve received shareholder benefits of \$300. The current stock price is \$13.72. The plan is to use the shareholder benefit many times and then sell the stock for the same or higher price. Well, given the disruption to the cruise industry and the dilution of NCLH, getting the original price in the future may not be possible.
For this analysis assume the following: - two cruises per year, totaling \$200 in shareholder benefit which will be called a dividend.
Calculate shareholder’s benefit per 100 shares and update the df_metrics_data[‘dps’] data.
df_metrics_data['dps'] = np.ones(len(df_metrics_data['dps']))*200/100 # shareholder benefit per year per share owned# calculate the percent change in dividends
pcd = np.zeros(len(df_metrics_data['dps'])) # percent change in dividend
for i in range(len(df_metrics_data['dps'][0:-1])):
pcd[i+1] = ((df_metrics_data['dps'][i+1] - df_metrics_data['dps'][i])/
df_metrics_data['dps'][i+1])*100
width = 100
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Dividend per share, $')
# plot revenue as single bar
plt.bar(df_metrics_data['FY'],df_metrics_data['dps'], width,color='k')
ax1.tick_params(axis='y')
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:green'
ax2.plot(year_ended_list,pcd,'+-g')
ax2.set_ylabel('% Change in dividend',color=color)
ax2.tick_params(axis='y', labelcolor=color)
ax2.set_ylim((0,20))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Dividend history per share')
plt.show()
adgr = pcd[-6:].mean() #last 6 years
print('average dividend growth rate: {:.2f}%'.format(adgr))average dividend growth rate: 0.00%Dividend yield equals the annual dividend per share divided by the stock’s price per share. The plot below shows the history of dividend yield over the evaluation period.
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
width = 50 # the width of the bars
plt.bar(df_metrics_data['FY'],(df_metrics_data['dps']/fy_high-df_metrics_data['dps']/fy_low)*100,
width,bottom=df_metrics_data['dps']/fy_low*100,label='yield')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.ylim((0,30))
plt.title('Range of dividend yield each year')
plt.ylabel('dividend yield, %')
#plt.legend()
plt.grid()
# show plot
plt.show()
The internal rate of return (IRR) is the discount rate that makes the net present value (NPV) of all cash flows equal to zero in a discounted cash flow analysis. Generally speaking, the higher an internal rate of return, the more desirable an investment is to undertake.
As explained above, the stock price has not changed by much over the years, even though the revenue and dividends have been increasing. The final stock price is set equal to the current price.
Using the average dividend growth rate calculated above, a series of estimated future dividend payments are generated.
fdp = np.zeros(len(df_metrics_data['dps'])) # future dividend payments
fdp[0] = df_metrics_data['dps'].iat[-1]
for i in range(len(df_metrics_data['dps'][0:-1])):
fdp[i+1] = fdp[i]+fdp[i]*adgr/100print('current stock price: ${:,.2f}'.format(csp))
fsp = 20 #100 #csp #500 #(csp + 102.05 + 138.82)/3 # final stock price, $
print('final stock price: ${:,.2f}'.format(fsp))current stock price: $13.72
final stock price: $20.00est_cf = np.copy(fdp) # make a copy of the estimated cash flow
# cash flows, initial purchase, dividend payments and final sale
est_cf[0] = est_cf[0] - 42.9 # subtract purchase price from the first dividend payment
est_cf[-1] = est_cf[-1] + fsp # include the sale price with the final dividend paymentdividend_irr = np_financial.irr(est_cf)
print('IRR: {:.2f}%'.format(dividend_irr*100))IRR: -0.29%A negative IRR indicates that my shareholders benefit will not break even with a final stock price of \$20.
The following analysis somewhat follows the Warren Buffett strategy as outlined in [3]. This strategy is essentially value investing where companies are chosen that meet a set of criteria and who’s stock price is below the intrinsic value plus a margin of safety. These investments are usually held for the long term.
The following financial metrics are examined over the evaluation period. We are looking for favorable trends and evidence of consistent operations. Some red flags will also be evident in the plots.
Red flags:
- Shrinking gross profit margin - Receivables growing faster than sales - Rising debt-to-equity ratio - Several years of revenue trending down - Unsteady cash flow - Rising accounts receivable or inventory in relation to sales - Rising outstanding share count - Consistently higher liabilities than assets - Decreasing gross profit margin - Increasing revenue while cash flow remains the same - Unusual changes in key financial ratios
The ratio of liabilities to assets is plotted over the evaluation period. For most companies examined the liabilities are the total liabilities and the ratio is calculated using total assets and total tangible assets. Total tangible assets have goodwill and intangibles removed from the total. The ratio gives an indication of how much the company is worth versus how much the company owes. Ideally the ratio of liabilities to assets should be less than one.
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('ratio')
# plot revenue as single bar
ax1.plot(df_metrics_data['FY'],df_metrics_data['total_liabilities']/df_metrics_data['total_assets'], '-+',
label='total liabilities to total assets')
ax1.plot(df_metrics_data['FY'],df_metrics_data['total_liabilities']/df_metrics_data['total_tangible_assets'], '-*',
label='total liabilities to total tangible assets')
ax1.tick_params(axis='y')
ax1.set_ylim((0,1))
ax1.legend(bbox_to_anchor=(1.8, 1))
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:green'
#ax2.plot(year_ended_list,pcd,'+-g')
ax2.plot(df_metrics_data['FY'],
(df_metrics_data['total_assets']-df_metrics_data['total_tangible_assets'])/df_metrics_data['total_assets']*100,
':',color=color,label='intangible assets to total assets')
ax2.set_ylabel('% intangible assets',color=color)
ax2.tick_params(axis='y', labelcolor=color)
ax2.set_ylim((0,100))
ax2.legend(bbox_to_anchor=(1.7, 0))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Total liabilities to total assets ratio')
plt.show()
For the years 2020 and 2021, the ratio of total liabilities to total tangible assets has risen on account of borrowing during the pandemic.
The value assigned to goodwill and intangibles is about \$?? billion.
The percentage of intangible assets to total assets is also plotted. The ratio indicates the company has taken on a lot of debt relative to assets and is something of concern.
The level of intangible assets are low.
The debt-to-equity ratio (D/E) is another key characteristic Buffett considers carefully. Buffett prefers to see a small amount of debt so that earnings growth is being generated from shareholders’ equity as opposed to borrowed money. The D/E ratio is calculated as follows:
\(\text{Debt-to-Equity Ratio} = \frac {\text{Total Liabilities}} {\text{Shareholders' Equity}} \text{ OR } \frac {\text{Long term debt}} {\text{Shareholders' Equity}}\)
This ratio shows the proportion of equity and debt the company uses to finance its assets, and the higher the ratio, the more debt—rather than equity—is financing the company. A high debt level compared to equity can result in volatile earnings and large interest expenses. For a more stringent test, investors sometimes use only long-term debt instead of total liabilities in the calculation above.
D/E is the traditional way to look at a company’s debt. Some rules of thumb say that the D/E should not be above 2 or 3. However the D/E company’s typically vary by industry. The ratio of LT debt to NOP gives the number of years it would take the company to pay back debt from NOP, the lower the number the shorter amount of time.
\(\text{Debt-to-NOP Ratio} = \frac {\text{Total Liabilities}} {\text{NOP}}\)
tangible_equity = df_metrics_data['total_tangible_assets'] - df_metrics_data['total_liabilities']
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('ratio')
ax1.plot(df_metrics_data['FY'],df_dcf_data['long_term_debt']/df_metrics_data['shareholder_equity'],
'-^',label='(LT debt)/Equity')
#ax1.plot(year_ended_list,df_dcf_data['long_term_debt']/tangible_equity, '-',label='(LT debt)/(Tangible Equity)')
ax1.plot(df_metrics_data['FY'],df_metrics_data['total_liabilities']/df_metrics_data['shareholder_equity'],
'-*',label='(total liabilities)/Equity')
#ax1.plot(year_ended_list,total_liabilities/BV, '-^',label='(total liabilities)/BV')
ax1.plot(df_metrics_data['FY'],df_metrics_data['total_liabilities']/nop, '-+',label='(total liabilities)/NOP')
#ax1.plot(year_ended_list,total_liabilities/net_income, '-+',label='(total liabilities)/(net income)')
#ax1.plot(year_ended_list,df_dcf_data['current_liabilities']/nop, '-*',label='(current liabilities)/NOP')
#ax1.plot(year_ended_list,Liabilities_wo_deposits/nop, '-+',label='(Liabilities w/o deposits)/NOP')
ax1.tick_params(axis='y')
ax1.set_ylim((0,10))
#ax1.legend()
ax1.legend(bbox_to_anchor=(1.6, 1))
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Various debt ratios')
plt.show()
(LT debt)/Equity is plotted and prior to 2019 was less than the threshold of 2.
A threshold of 2 is traditionally the upper limit for a reasonable amount of debt that a company should carry.
(total liabilities)/Equity is plotted and prior to 2019 was less than the threshold of 2.
(total liabilities)/NOP to is plotted for each year in the evaluation period and prior to 2019 was less than the threshold of 10.
A value of 10 has been chosen as the threshold for this ratio and indicates how many years it would take the company to pay off total liabilities from the NOP generated each year. A threshold of ten seems like a reasonable level of debt measured against NOP.
Various ratios can be used to judge management performance. Consistent favorable trends are an indication that management is taking care of the company.
Return on equity
Sometimes return on equity (RoE) is referred to as stockholder’s return on investment. It reveals the rate at which shareholders earn income on their shares. Buffett always looks at RoE to see whether a company has consistently performed well compared to other companies in the same industry. RoE is calculated as follows:
\(\text{Return on Equity} = \frac {\text{Net Income}} {\text{Shareholder's Equity}}\)
Looking at the RoE in just the last year isn’t enough. The investor should view the RoE from the past five to 10 years to analyze historical performance.
\(\text{Shareholders’ Equity} = \text{Total Assets} − \text{Total Liabilities}\)
For this company, this method of getting Shareholders’ Equity gives negative values. On the Consolidated Balance Sheets, there is a line for Total stockholders’ equity, which is used.
Return on Assets
Return on assets is a profitability ratio that provides how much profit a company is able to generate from its assets. In other words, return on assets (RoA) measures how efficient a company’s management is in generating earnings from their economic resources or assets on their balance sheet.
\(\text{Return on assets} = \frac {\text{Net Income}} {\text{Total Assets}}\)
Calculating the RoA of a company can be helpful in comparing a company’s profitability over multiple quarters and years as well as comparing to similar companies. However, it’s important to compare companies of similar size and industry.
For example, banks tend to have a large number of total assets on their books in the form of loans, cash, and investments. A large bank could easily have over \$2 trillion in assets while putting up a net income that’s similar to companies in other industries. Although the bank’s net income or profit might be similar to an unrelated company and the bank might have high-quality assets, the bank’s RoA will be lower. The larger number of total assets must be divided into the net income, creating a lower RoA for the bank.
Similarly, auto manufacturing requires huge facilities and specialized equipment. A lucrative software company that sells downloadable programs online may generate the same net profits, but it could have a significantly higher RoA than its more asset-heavy counterparts. When utilizing this metric to compare productivity across businesses, it’s important to take into account what types of assets are required to function in a given industry, rather than simply comparing the figures.
Profit Margin
A company’s profitability depends not only on having a good profit margin, but also on consistently increasing it. This margin is calculated by dividing net income by net sales. For a good indication of historical profit margins, investors should look back at least five years. A high-profit margin indicates the company is executing its business well, but increasing margins mean management has been extremely efficient and successful at controlling expenses.
\(\text{Profit Margin} = \frac {\text{Net Income}} {\text{Revenue}}\)
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('percent')
ax1.plot(df_metrics_data['FY'],df_metrics_data['net_income']/df_metrics_data['shareholder_equity']*100,
'-+',label='RoE')
ax1.plot(df_metrics_data['FY'],df_metrics_data['net_income']/df_metrics_data['total_assets']*100,
'-*',label='RoA')
#ax1.plot(df_metrics_data['FY'],total_liabilities/shareholder_equity, '-^',label='D/E')
ax1.plot(df_metrics_data['FY'],df_metrics_data['net_income']/df_dcf_data['revenue']*100,
'-^',label='Profit margin')
ax1.tick_params(axis='y')
ax1.set_ylim((-10,20))
#ax1.legend()
ax1.legend(bbox_to_anchor=(1.05, 1))
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Various ratios')
plt.show()
Observation:
The trends for RoE, RoA and profit margin are shown above. The effect of the pandemic caused the ratios to turn negative and are no longer a meaningful metric.
See page 86, figure 9-1.
- % pretax profit on sales, (net before taxes)/rev - % earned on equity (another way of saying RoE, using calculated equity)
Percent earned on equity is a measure of financial performance calculated by dividing net income by equity. Because equity is equal to a company’s assets minus its debt, percent earned on equity is considered the return on net assets. Percent earned on equity is considered a gauge of a corporation’s profitability and how efficient it is in generating profits.
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('percent')
#ax1.plot(year_ended_list,net_income, '-+',label='net income')
ax1.plot(df_metrics_data['FY'],df_dcf_data['income_before_income_taxes']/df_dcf_data['revenue']*100, '-+',
label='income before taxes/rev')
#ax1.plot(year_ended_list,df_dcf_data['revenue'], '-+',label='revenue')
#ax1.plot(year_ended_list,free_cash_flow, '-*',label='free cash flow')
ax1.tick_params(axis='y')
ax1.set_ylim((0,20))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('% pretax profit on sales')
plt.show()
Over the years 2016 to 2020, pretax profit on sales has a downward trend. Ideally this trend should be increasing or at least flat.
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('percent')
#ax1.plot(year_ended_list,shareholder_equity/df_dcf_data['revenue']*100, '-+k',
# label='shareholder equity/rev')
#ax1.plot(year_ended_list,net_income/shareholder_equity*100, '-+',label='RoE')
ax1.plot(df_metrics_data['FY'],
df_metrics_data['net_income']/(df_metrics_data['total_assets']-df_metrics_data['total_liabilities'])*100,
'-+',label='RoE')
ax1.tick_params(axis='y')
ax1.set_ylim((0,20))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('% earned on equity')
plt.show()
Percent earned on equity (another way of saying RoE). Percent earned on equity trend has been flat up to 2015, then became erratic.
Percent earned on equity is a measure of financial performance calculated by dividing net income by equity. Because equity is equal to a company’s assets minus its debt, percent earned on equity is considered the return on net assets. Percent earned on equity is considered a gauge of a corporation’s profitability and how efficient it is in generating profits.
The following charts examine data from the consolidated financial statements and compare normalized trends over the evaluation period. The first chart plots normalized revenue along with normalized EPS, NOP and free cash flow. All values are normalized to the starting value in the series. Change relative to the normalized starting value can be seen over the evaluation period. Ideally the normalized parameters plotted should track revenue. Any large departures indicate an area of concern.
The following chart shows normalized revenue plotted with normalized parameters from the consolidated statement of income.
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
# set look back range, left_yr is the index into the date range
left_yr = -10
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['revenue'][left_yr:]-df_dcf_data['revenue'].iloc[left_yr])/np.abs(df_dcf_data['revenue'].iloc[left_yr])*100,
'^-',label='Revenue')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['eps'][left_yr:]-df_metrics_data['eps'].iloc[left_yr])/np.abs(df_metrics_data['eps'].iloc[left_yr])*100,
'-.',label='EPS')
plt.plot(df_metrics_data['FY'][left_yr:],
(nop[left_yr:]-nop[left_yr])/np.abs(nop[left_yr])*100,
'-.',label='NOP')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['free_cash_flow'][left_yr:]-df_metrics_data['free_cash_flow'].iloc[left_yr])/np.abs(df_metrics_data['free_cash_flow'].iloc[left_yr])*100,
'-.',label='Free cash flow')
# net income
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['net_income'][left_yr:]-df_metrics_data['net_income'].iloc[left_yr])/np.abs(df_metrics_data['net_income'].iloc[left_yr])*100,
'-.',label='Net income')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
plt.gca().set_xbound(year_ended_list[left_yr], year_ended_list[-1])
plt.ylim((-100,500))
plt.title('Normalized income statement data')
plt.ylabel('percent change')
#plt.legend()
plt.legend(bbox_to_anchor=(1.6, 1))
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
# set look back range, left_yr is the index into the date range
left_yr = -6
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['revenue'][left_yr:]-df_dcf_data['revenue'].iloc[left_yr])/np.abs(df_dcf_data['revenue'].iloc[left_yr])*100,
'^-',label='Revenue')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['eps'][left_yr:]-df_metrics_data['eps'].iloc[left_yr])/np.abs(df_metrics_data['eps'].iloc[left_yr])*100,
'-.',label='EPS')
plt.plot(df_metrics_data['FY'][left_yr:],
(nop[left_yr:]-nop[left_yr])/np.abs(nop[left_yr])*100,
'-.',label='NOP')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['free_cash_flow'][left_yr:]-df_metrics_data['free_cash_flow'].iloc[left_yr])/np.abs(df_metrics_data['free_cash_flow'].iloc[left_yr])*100,
'-.',label='Free cash flow')
# net income
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['net_income'][left_yr:]-df_metrics_data['net_income'].iloc[left_yr])/np.abs(df_metrics_data['net_income'].iloc[left_yr])*100,
'-.',label='Net income')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
plt.gca().set_xbound(year_ended_list[left_yr], year_ended_list[-1])
plt.ylim((-100,100))
plt.title('Normalized income statement data')
plt.ylabel('percent change')
#plt.legend()
plt.legend(bbox_to_anchor=(1.6, 1))
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
The plot above shows a large departure of revenue in 2020.
The following chart shows normalized revenue plotted with normalized parameters from the consolidated balance sheet.
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
# set look back range, left_yr is the index into the date range
left_yr = -10
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['revenue'][left_yr:]-df_dcf_data['revenue'].iloc[left_yr])/np.abs(df_dcf_data['revenue'].iloc[left_yr])*100,
'^-',label='Revenue')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['total_liabilities'][left_yr:]-df_metrics_data['total_liabilities'].iloc[left_yr])/np.abs(df_metrics_data['total_liabilities'].iloc[left_yr])*100,
'-.',label='Total liabilities')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['total_assets'][left_yr:]-df_metrics_data['total_assets'].iloc[left_yr])/np.abs(df_metrics_data['total_assets'].iloc[left_yr])*100,
'-.',label='Total assets')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['total_tangible_assets'][left_yr:]-df_metrics_data['total_tangible_assets'].iloc[left_yr])/np.abs(df_metrics_data['total_tangible_assets'].iloc[left_yr])*100,
'-.',label='Total tangible assets')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['long_term_debt'][left_yr:]-df_dcf_data['long_term_debt'].iloc[left_yr])/np.abs(df_dcf_data['long_term_debt'].iloc[left_yr])*100,
'-.',label='Long term debt')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['current_liabilities'][left_yr:]-df_dcf_data['current_liabilities'].iloc[left_yr])/np.abs(df_dcf_data['current_liabilities'].iloc[left_yr])*100,
'-.',label='Current liabilities')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['depreciation'][left_yr:]-df_dcf_data['depreciation'].iloc[left_yr])/np.abs(df_dcf_data['depreciation'].iloc[left_yr])*100,
'-.',label='Depreciation & amortization')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
plt.gca().set_xbound(year_ended_list[left_yr], year_ended_list[-1])
#plt.ylim((0,4))
plt.title('Normalized balance statement data')
plt.ylabel('percent change')
#plt.legend()
plt.legend(bbox_to_anchor=(1.6, 1))
plt.grid()
# show plot
plt.show()
Observation
Balance sheet items generally followed revenue up to 2020, then as revenue tanked, balance sheet items remained generally on the same trajectory.
# Set the locator
locator = mdates.YearLocator() # every year
fmt = mdates.DateFormatter('%Y')
# set look back range, left_yr is the index into the date range
left_yr = -6
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['revenue'][left_yr:]-df_dcf_data['revenue'].iloc[left_yr])/np.abs(df_dcf_data['revenue'].iloc[left_yr])*100,
'^-',label='Revenue')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['total_liabilities'][left_yr:]-df_metrics_data['total_liabilities'].iloc[left_yr])/np.abs(df_metrics_data['total_liabilities'].iloc[left_yr])*100,
'-.',label='Total liabilities')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['total_assets'][left_yr:]-df_metrics_data['total_assets'].iloc[left_yr])/np.abs(df_metrics_data['total_assets'].iloc[left_yr])*100,
'-.',label='Total assets')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_metrics_data['total_tangible_assets'][left_yr:]-df_metrics_data['total_tangible_assets'].iloc[left_yr])/np.abs(df_metrics_data['total_tangible_assets'].iloc[left_yr])*100,
'-.',label='Total tangible assets')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['long_term_debt'][left_yr:]-df_dcf_data['long_term_debt'].iloc[left_yr])/np.abs(df_dcf_data['long_term_debt'].iloc[left_yr])*100,
'-.',label='Long term debt')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['current_liabilities'][left_yr:]-df_dcf_data['current_liabilities'].iloc[left_yr])/np.abs(df_dcf_data['current_liabilities'].iloc[left_yr])*100,
'-.',label='Current liabilities')
plt.plot(df_metrics_data['FY'][left_yr:],
(df_dcf_data['depreciation'][left_yr:]-df_dcf_data['depreciation'].iloc[left_yr])/np.abs(df_dcf_data['depreciation'].iloc[left_yr])*100,
'-.',label='Depreciation & amortization')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
plt.gca().set_xbound(year_ended_list[left_yr], year_ended_list[-1])
#plt.ylim((0,4))
plt.title('Normalized balance statement data')
plt.ylabel('Percent change')
#plt.legend()
plt.legend(bbox_to_anchor=(1.6, 1))
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
The share price is determined by the market. The value is determined by the analyst.
This is a financial test that shows the strength of the business and how well management has rationality allocated to the company’s business.
From a company’s income, subtract all dividends paid to shareholders. What is left over is the company’s retained earnings. Now add the company’s retained earnings over a 10 year period. Next determine the difference between the company’s current market value and its market value 10 years ago. If the business has employed retained earnings unproductively over this ten year period, the market eventually catches up and will set a lower price on the business.
Only use pre covid data.
retained_earnings = df_metrics_data['net_income'][-6:-2].sum() - df_metrics_data['dividends'][-6:-2].sum()
print('retained earnings: ${:,.2f}B'.format(retained_earnings/1e9))retained earnings: $-1.37B# Current market value, share price multiplied by number of shares
cmv_high = df_dcf_data['shares_outstanding'].iloc[-3]*fy_high[-3]
cmv_low = df_dcf_data['shares_outstanding'].iloc[-3]*fy_low[-3]
print('Current market value: ${:,.2f}B to ${:,.2f}B'.format(cmv_low/1e9,cmv_high/1e9))Current market value: $1.98B to $15.19B# Past market value, share price multiplied by number of shares
pmv_high = df_dcf_data['shares_outstanding'].iloc[1]*fy_high[1]
pmv_low = df_dcf_data['shares_outstanding'].iloc[1]*fy_low[1]
print('Past market value: ${:,.0f}B to ${:,.0f}B'.format(pmv_low/1e9,pmv_high/1e9))Past market value: $5B to $7Bprint('Difference in market value: ${:,.0f}B to ${:,.0f}B'.format((cmv_low-pmv_low)/1e9,(cmv_high-pmv_high)/1e9))Difference in market value: $-3B to $8BThis difference in market value is greater than the retained earnings.
Looking at the one dollar premise in terms of share price and EPS.
The one dollar premise: one dollar of earning should translate into one dollar of market value - this seems the same as a plot of EPS versus share price.
# plotting the eps data points
ax = plt.bar(df_metrics_data['eps'][1:-2],fy_high[1:-2]-fy_low[1:-2],width = .05,bottom=fy_low[1:-2])
plt.grid()
plt.ylim((20,80))
plt.ylabel('high and low share price range')
plt.xlabel('EPS')
plt.title('years from 2010 to 2021')
rects = ax.patches
# Make some labels.
labels = [year_ended_list[i].strftime("%Y") for i in range(len(year_ended_list))]
for rect, label in zip(rects, labels):
y_top = rect.get_y() + rect.get_height()
plt.text(rect.get_x(), y_top+1, label, rotation=90,va='bottom')
plt.show()
Observations:
The range in share price is roughly the same across the range of EPS. This means that investors are not valuing the company’s EPS.
Beyond the numbers in the financial statements, there are metrics that are qualitative in nature that are important to the investor. These are subjective measures of business and management operations that influence value. In this section a few qualitative metrics are discussed below.
Simple and understandable business model
The business is to provide a vacation at sea.
Favorable long term prospects
Poor, with the threat of inflation, higher fuel prices, higher interest rates and low ticket demand.
Commodity reliance
NCL provides a service which is easily reproducible by a competitor.
Consistent operating history
Seemed consistent prior to the pandemic, but unknown going forward.
Rationality
Focus on core aspects
Yes, cruising is the only business NCL operates.
Only invest in high ROE businesses
NA
Focus on shareholder equity
Survival of the company during and after the pandemic has been the focus.
NCLH significantly reduced their marketing activities in 2020 due to the suspension of cruise voyages as a result of the COVID-19 pandemic. Sales and marketing activities have increased in an attempt to further drive demand. Additionally, they continue a deliberate approach on marketing and sales outreach to guests with future cruise credits, as a result of suspended sailings, to encourage redemption of cruise credits towards future sailings. Building customer loyalty among past guests is an important element of marketing strategy. Past guests create a cost-effective means of attracting business, particularly to new ships and itineraries as they are familiar with the brands, products and services and often return to cruise with NCLH.
Fleet Expansion
For the Norwegian brand, Project Leonardo will introduce six additional ships, each ranging from approximately 140,000 to 156,300 Gross Tons with approximately 3,300 to 3,550 Berths, with expected delivery dates from 2022 through 2027. For the Oceania Cruises brand, NCLH has orders for two Allura Class Ships to be delivered in 2023 and 2025. Each of the Allura Class Ships will be approximately 67,000 Gross Tons and 1,200 Berths. For the Regent brand, NCLH has one Explorer Class Ship on order to be delivered in 2023, which will be approximately 55,000 Gross Tons and 750 Berths.
| Norwegian | Oceania | Regent |
|---|---|---|
| Prima Class: 6 ships 2022 to 2027 | Allura Class: 2 ships 2023 and 2025 | Explorer Class: 1 ship 2023 |
| Norwegian Encore | Oceania Riviera | Seven Seas Splendor |
| Norwegian Bliss | Oceania Marina | Seven Seas Explorer |
| Norwegian Joy | Oceania Nautica | Seven Seas Voyager |
| Norwegian Escape | Oceania Sirena | Seven Seas Mariner |
| Norwegian Getaway | Oceania Regatta | Seven Seas Navigator |
| Norwegian Breakaway | Oceania Insignia | |
| Norwegian Epic | ||
| Norwegian Gem | ||
| Norwegian Jade | ||
| Norwegian Pearl | ||
| Norwegian Jewel | ||
| Pride of America | ||
| Norwegian Dawn | ||
| Norwegian Star | ||
| Norwegian Sun | ||
| Norwegian Sky | ||
| Norwegian Spirit |
Ships represent the most significant assets, and NCLH records them at cost less accumulated depreciation. Depreciation of ships is computed on a straight-line basis over the weighted average useful lives of primarily 30 years after a 15% reduction for the estimated residual value of the ship. Ship improvement costs that NCLH believes add value to our ships are capitalized to the ship and depreciated over the shorter of the improvements’ estimated useful lives or the remaining useful life of the ship. When they record the retirement of a ship component included within the ship’s cost basis, they estimate the net book value of the component being retired and remove it from the ship’s cost basis. Repairs and maintenance activities are charged to expense as incurred. NCLH accounts for Dry-dock costs under the direct expense method which requires us to expense all Dry-dock costs as incurred.
NCLH determines the weighted average useful lives of our ships based primarily on our estimates of the useful lives of the ships’ major component systems on the date of acquisition, such as cabins, main diesels, main electric, superstructure and hull. The useful lives of ship improvements are estimated based on the economic lives of the new components. In addition, to determine the useful lives of the ship or ship components, thay consider the impact of the historical useful lives of similar assets, manufacturer recommended lives and anticipated changes in technological conditions. Given the large and complex nature of our ships, their accounting estimates related to ships and determinations of ship improvement costs to be capitalized require judgment and are uncertain. Should certain factors or circumstances cause them to revise their estimate of ship service lives or projected residual values, depreciation expense could be materially lower or higher. In 2020, one ship had significant improvements that extended the remaining weighted average useful life of the vessel. Accordingly, They have updated our estimate of both its useful life and residual value based on the new weighted average useful life of its current components. The impact of the change in estimate is accounted for on a prospective basis and is not material.
If circumstances cause NCLH to change their assumptions in making determinations as to whether ship improvements should be capitalized, the amounts they expense each year as repairs and maintenance costs could increase, partially offset by a decrease in depreciation expense. If they reduced their estimated weighted average 30-year ship service life by one year, depreciation expense for the year ended December 31, 2020 would have increased by \$19.8 million. In addition, if their ships were estimated to have no residual value, depreciation expense for the same period would have increased by \$99.6 million. We believe their estimates for ship accounting are reasonable and their methods are consistently applied. They believe that depreciation expense is based on a rational and systematic method to allocate their ships’ costs to the periods that benefit from the ships’ usage.
Back to Contents
The decision model establishes thresholds that are to be used in the purchase decision. There are three hard decision thresholds in this model which are:
1. Intrinsic value 2. Debt 3. Dividend payout ratio 4. Dividend IIR
The first threshold is based on the intrinsic value of the company as calculated by the DCF model scenario 1. Recognizing that absolute intrinsic value is an elusive concept, judgment, justified by facts (assets, earnings, dividends, debt and cash flow), establishes the value by adjusting various rates, based on judgment and using a five year forward projection period. This should give an intrinsic value that is based on the historical data, modified by judgment.
I’m using a threshold of the intrinsic value calculated in scenario 1 (isv_S1) that is greater than 70% of the current stock price, provided that the NAIC valuation is above the current stock price. This accounts for the inadequacy or incorrectness of the data, the uncertainties of the future, and considers the behavior of the market.
The second threshold is the level of debt. The ratios of (LT debt)/Equity, (total liabilities)/Equity and (total liabilities)/NOP are plotted for the evaluation period. Over the evaluation period the (LT debt)/Equity and (total liabilities)/Equity should be less than 2 and stable. A threshold of 2 has been discussed in the literature as a level of debt that a company can reasonably take on.
The threshold for (total liabilities)/NOP is set at 10. This means that the company can pay off all the liabilities with ten years worth of NOP, which seems like a reasonable time frame for an established and stable company.
The third threshold is the dividend payout ratio and is a relative measure of how much the company is paying to shareholders in dividends compared to the metrics of NOP and free cash flow (Net cash provided by operating activities). The payout ratio is useful for assessing a dividend’s sustainability. Payout ratio for a REIT is established by tax law and not used as an evaluation criteria. For other industries a threshold of 50% has been set as the limit.
The dividend IRR threshold is the internal rate of return for investor dividend cash flow (divident_irr) should be greater than 10 year treasury bond yield (tby) plus the equity risk premium (eq_rp). Otherwise, other investment opportunities should be looked at.
In the decision model there are soft thresholds based on judgment. Soft thresholds are a collection of ratios and analysis that taken together tell a story of the performance of the company and management’s ability to run the company and support dividends over the long term. Use judgment and make an evaluation.
The third criteria is a collection of ratios and analysis that taken together tell a story of the performance of the company and management’s ability to run the company and support dividends over the long term. Use judgment and make an evaluation. These are the following:
1. Financial metrics 2. Market metrics 3. Qualitative metrics
The soft thresholds are discussed in section 10. These metrics only look at data prior to 2019.
# check DCF scenario 1
dcf_score = isv_S1/csp #ratio of isv to csp
dcf_threshold = 0.7
if dcf_score < 0.7:
print('FAIL, DCF score is less than {:.1f} at {:.1f}'.format(dcf_threshold,dcf_score))
else:
print('PASS, DCF score is above {:.1f} at {:.1f}'.format(dcf_threshold,dcf_score))
# check NAIC
naic_score = np.array([naic_price_eps_low,naic_price_pm_low]).min()/csp
naic_threshold = 1
if naic_score < 1:
print('FAIL, NAIC score is less than {:.1f} at {:.1f}'.format(naic_threshold,naic_score))
else:
print('PASS, NAIC score is above {:.1f} at {:.1f}'.format(naic_threshold,naic_score))
# check both scores
if naic_score < 1 or dcf_score < 0.7:
print('One or both DCF and NAIC scores failed')
else:
print('Both DCF and NAIC scores pass')FAIL, DCF score is less than 0.7 at -1.3
FAIL, NAIC score is less than 1.0 at -6.0
One or both DCF and NAIC scores faileddebt_lookback = 6
avg_LT_debt2EQ = df_dcf_data['long_term_debt'][-debt_lookback:-2].mean()/df_metrics_data['shareholder_equity'][-debt_lookback:-2].mean()
avg_TLiability2EQ = df_metrics_data['total_liabilities'][-debt_lookback:-2].mean()/df_metrics_data['shareholder_equity'][-debt_lookback:-2].mean()
avg_TLiability2NOP = df_metrics_data['total_liabilities'][-debt_lookback:-2].mean()/nop[-debt_lookback:-2].mean()
print('long term debt to shareholder equity ratio = {:.2f}'.format(avg_LT_debt2EQ))
print('total liabilities to shareholder equity ratio = {:.2f}'.format(avg_TLiability2EQ))
print('total liabilities to NOP ratio = {:.2f}'.format(avg_TLiability2NOP))
if (avg_LT_debt2EQ > 2) or (avg_TLiability2EQ > 2) or (avg_TLiability2NOP > 10):
print('FAILED one of the debt threshold limits')
else:
print('passed debt threshold limits')long term debt to shareholder equity ratio = 1.36
total liabilities to shareholder equity ratio = 1.85
total liabilities to NOP ratio = 10.44
FAILED one of the debt threshold limits# check dividend payout ratio average the last three years
print('Dividends are paid at {:.1f}% of cash flow'.format(
(df_metrics_data['dividends']/df_metrics_data['free_cash_flow'])[-3:].mean()*100))
print('Dividends are paid at {:.1f}% of NOP'.format((df_metrics_data['dividends']/nop)[-3:].mean()*100))
if ((df_metrics_data['dividends']/nop)[-3:].mean() or (df_metrics_data['dividends']/df_metrics_data['free_cash_flow'])[-3:].mean()) > 0.5:
print('FAIL, dividend payout ratio too high')Dividends are paid at 0.0% of cash flow
Dividends are paid at 0.0% of NOP# Check dividend IRR limit
if dividend_irr < (tby+eq_rp):
print('FAIL, dividend IRR is less than {:.2f} at {:.2f}'.format((tby+eq_rp)*100,dividend_irr*100))
else:
print('PASS, dividend IRR is above {:.2f} at {:.2f}'.format((tby+eq_rp)*100,dividend_irr*100))FAIL, dividend IRR is less than 6.04 at -0.29Quarterly financial reports were obtained from the company’s investor relations web page. These reports are available in pdf, spreadsheet or XBRL format. Data from the NCL’s spreadsheets was copied and organized into a new spreadsheet to be used in this notebook.
The python code below reads the new excel spreadsheet and converts the data into a pandas dataframe. Dollar amounts are converted from thousands of dollars to straight dollars. Dates are converted from a character string to datetime format.
The code below reads the data from the excel sheet which has already been converted into a pandas dataframe and puts the data into temporary variables.
#column names: dates for ending of quarter
qrtr_dates = df_qrt_sheet.columns[1:].values.astype('datetime64[D]')
# load spreadsheet row data from sheet into temp variables
revenue_qrtr_data = df_qrt_sheet.iloc[0].to_numpy()[1:].astype('float')
cruise_operating_expense_qrtr_data = df_qrt_sheet.iloc[1].to_numpy()[1:].astype('float')
marketing_general_and_administrative_qrtr_data = df_qrt_sheet.iloc[2].to_numpy()[1:].astype('float')
depreciation_qrtr_data = df_qrt_sheet.iloc[3].to_numpy()[1:].astype('float')
other_operating_expense_qrtr_data = df_qrt_sheet.iloc[4].to_numpy()[1:].astype('float')
operating_income_qrtr_data = df_qrt_sheet.iloc[5].to_numpy()[1:].astype('float')
cash_and_cash_equivalents_qrtr_data = df_qrt_sheet.iloc[6].to_numpy()[1:].astype('float')
accounts_receivable_qrtr_data = df_qrt_sheet.iloc[7].to_numpy()[1:].astype('float')
inventories_qrtr_data = df_qrt_sheet.iloc[8].to_numpy()[1:].astype('float')
prepaid_expenses_and_other_assets_qrtr_data = df_qrt_sheet.iloc[9].to_numpy()[1:].astype('float')
total_current_assets_qrtr_data = df_qrt_sheet.iloc[10].to_numpy()[1:].astype('float')
property_and_equipment_qrtr_data = df_qrt_sheet.iloc[11].to_numpy()[1:].astype('float')
goodwill_qrtr_data = df_qrt_sheet.iloc[12].to_numpy()[1:].astype('float')
trade_names_qrtr_data = df_qrt_sheet.iloc[13].to_numpy()[1:].astype('float')
other_long_term_assets_qrtr_data = df_qrt_sheet.iloc[14].to_numpy()[1:].astype('float')
total_assets_qrtr_data = df_qrt_sheet.iloc[15].to_numpy()[1:].astype('float')
current_portion_of_long_term_debt_qrtr_data = df_qrt_sheet.iloc[16].to_numpy()[1:].astype('float')
accounts_payable_qrtr_data = df_qrt_sheet.iloc[17].to_numpy()[1:].astype('float')
accrued_expenses_and_other_liabilities_qrtr_data = df_qrt_sheet.iloc[18].to_numpy()[1:].astype('float')
advance_ticket_sales_qrtr_data = df_qrt_sheet.iloc[19].to_numpy()[1:].astype('float')
total_current_liabilities_qrtr_data = df_qrt_sheet.iloc[20].to_numpy()[1:].astype('float')
long_term_debt_qrtr_data = df_qrt_sheet.iloc[21].to_numpy()[1:].astype('float')
other_long_term_liabilities_qrtr_data = df_qrt_sheet.iloc[22].to_numpy()[1:].astype('float')
total_liabilities_qrtr_data = df_qrt_sheet.iloc[23].to_numpy()[1:].astype('float')
net_loss_qrtr_data = df_qrt_sheet.iloc[24].to_numpy()[1:].astype('float')
additions_to_property_and_equipment_qrtr_data = df_qrt_sheet.iloc[25].to_numpy()[1:].astype('float')
net_cash_provided_by_investing_activities_qrtr_data = df_qrt_sheet.iloc[26].to_numpy()[1:].astype('float')
repayments_of_long_term_debt_qrtr_data = df_qrt_sheet.iloc[27].to_numpy()[1:].astype('float')
proceeds_from_long_term_debt_qrtr_data = df_qrt_sheet.iloc[28].to_numpy()[1:].astype('float')
common_share_issuance_proceeds_qrtr_data = df_qrt_sheet.iloc[29].to_numpy()[1:].astype('float')
early_redemption_premium_qrtr_data = df_qrt_sheet.iloc[30].to_numpy()[1:].astype('float')
deferred_financing_fees_qrtr_data = df_qrt_sheet.iloc[31].to_numpy()[1:].astype('float')
net_cash_provided_by_financing_activities_qrtr_data = df_qrt_sheet.iloc[32].to_numpy()[1:].astype('float')
net_increase_in_cash_and_cash_equivalents_qrtr_data = df_qrt_sheet.iloc[33].to_numpy()[1:].astype('float')
cash_and_cash_equivalents_at_beginning_of_period_qrtr_data = df_qrt_sheet.iloc[34].to_numpy()[1:].astype('float')
cash_and_cash_equivalents_at_end_of_period_qrtr_data = df_qrt_sheet.iloc[35].to_numpy()[1:].astype('float')
weighted_average_shares_outstanding_basic_qrtr_data = df_qrt_sheet.iloc[36].to_numpy()[1:].astype('float')
future_cruise_credits_qrtr_data = df_qrt_sheet.iloc[37].to_numpy()[1:].astype('float')The code below takes the temporary variables, converts thousands of dollars to actual dollars, by multiplying by 1000, reverses the order of the data and stores the data back into a new dataframe.
# make a new data frame to store selected quarterly data
df_qrtr_data = pd.DataFrame(data={
'QTR':qrtr_dates[::-1],
'revenue':revenue_qrtr_data[::-1]*1000,
'cruise_operating_expense':cruise_operating_expense_qrtr_data[::-1]*1000,
'marketing_general_and_administrative':marketing_general_and_administrative_qrtr_data[::-1]*1000,
'depreciation':depreciation_qrtr_data[::-1]*1000,
'other_operating_expense':other_operating_expense_qrtr_data[::-1]*1000,
'operating_income':operating_income_qrtr_data[::-1]*1000,
'cash_and_cash_equivalents':cash_and_cash_equivalents_qrtr_data[::-1]*1000,
'accounts_receivable':accounts_receivable_qrtr_data[::-1]*1000,
'inventories':inventories_qrtr_data[::-1]*1000,
'prepaid_expenses_and_other_assets':prepaid_expenses_and_other_assets_qrtr_data[::-1]*1000,
'total_current_assets':total_current_assets_qrtr_data[::-1]*1000,
'property_and_equipment':property_and_equipment_qrtr_data[::-1]*1000,
'goodwill':goodwill_qrtr_data[::-1]*1000,
'trade_names':trade_names_qrtr_data[::-1]*1000,
'other_long_term_assets':other_long_term_assets_qrtr_data[::-1]*1000,
'total_assets':total_assets_qrtr_data[::-1]*1000,
'current_portion_of_long_term_debt':current_portion_of_long_term_debt_qrtr_data[::-1]*1000,
'accounts_payable':accounts_payable_qrtr_data[::-1]*1000,
'accrued_expenses_and_other_liabilities':accrued_expenses_and_other_liabilities_qrtr_data[::-1]*1000,
'advance_ticket_sales':advance_ticket_sales_qrtr_data[::-1]*1000,
'total_current_liabilities':total_current_liabilities_qrtr_data[::-1]*1000,
'long_term_debt':long_term_debt_qrtr_data[::-1]*1000,
'other_long_term_liabilities':other_long_term_liabilities_qrtr_data[::-1]*1000,
'total_liabilities':total_liabilities_qrtr_data[::-1]*1000,
'net_loss':net_loss_qrtr_data[::-1]*1000,
'additions_to_property_and_equipment':additions_to_property_and_equipment_qrtr_data[::-1]*1000,
'net_cash_provided_by_investing_activities':net_cash_provided_by_investing_activities_qrtr_data[::-1]*1000,
'repayments_of_long_term_debt':repayments_of_long_term_debt_qrtr_data[::-1]*1000,
'proceeds_from_long_term_debt':proceeds_from_long_term_debt_qrtr_data[::-1]*1000,
'common_share_issuance_proceeds':common_share_issuance_proceeds_qrtr_data[::-1]*1000,
'early_redemption_premium':early_redemption_premium_qrtr_data[::-1]*1000,
'deferred_financing_fees':deferred_financing_fees_qrtr_data[::-1]*1000,
'net_cash_provided_by_financing_activities':net_cash_provided_by_financing_activities_qrtr_data[::-1]*1000,
'net_increase_in_cash_and_cash_equivalents':net_increase_in_cash_and_cash_equivalents_qrtr_data[::-1]*1000,
'cash_and_cash_equivalents_at_beginning_of_period':cash_and_cash_equivalents_at_beginning_of_period_qrtr_data[::-1]*1000,
'cash_and_cash_equivalents_at_end_of_period':cash_and_cash_equivalents_at_end_of_period_qrtr_data[::-1]*1000,
'weighted_average_shares_outstanding_basic':weighted_average_shares_outstanding_basic_qrtr_data[::-1],
'future_cruise_credits':future_cruise_credits_qrtr_data[::-1]
})During the fourth quarter of 2021, the Company identified an error in its consolidated balance sheet as of September 30, 2021 and consolidated statement of cash flows for the nine months ended September 30, 2021. Based on their nature, certain amounts shown as cash and cash equivalents should have been classified as short-term investments. We have determined that these errors were not material to the previously issued interim financial statements for the period ended September 30, 2021.
As a result of the error, the amounts previously reported as cash and cash equivalents have been reclassified to cash flows used in investing activities in the consolidated statement of cash flows for the nine months ended September 30, 2021 as follows (in thousands):
NCL to Pay Travel Agent Partners Commission on Non-Commissionable Fares
Norwegian Cruise Line To Pay Travel Advisors Commission on Non-Commissionable Fares
NCL To Pay Travel Agents Commission on NCFs
The plot below shows the recent quarterly income plotted from the first quarter of 2019 to the second quarter of 2022. Revenue, operating income, advance ticket sales and cost of operations are plotted. Markers are included on the zero dollar line that indicate the dates for operations suspended, first ship to return to service and full fleet in operation. The dates for operations suspended (March 13, 2020), 1st ship return to service (July 25, 2021) and full fleet operating (May 7, 2022) are indicated on the plot with markers.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('dollars, $B')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['revenue']/1e9,'-+b',label='revenue')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['operating_income']/1e9,'-+r',label='operating income')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['advance_ticket_sales']/1e9,'-+g',label='advance ticket sales')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['cruise_operating_expense']/1e9,'-+c',label='cruise operating expense')
ax1.plot(pd.Timestamp(datetime(2020,3,13)),0,'^k',label='operations suspended, March 13') # March 13, 2020, operations suspended
ax1.plot(pd.Timestamp(datetime(2021,7,25)),0,'ob',label='1st ship return to service, July 25') # July 25, 2021, 1st ship return to service
ax1.plot(pd.Timestamp(datetime(2022,5,7)),0,'>k',label='full fleet operating, May 7') # May 7 2022, full fleet operating
ax1.tick_params(axis='y')
ax1.set_ylim((-2,3))
plt.legend(bbox_to_anchor=(1.7, 1))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Recent quarterly income items')
plt.show()
Revenue prior to the cruise industry shutdown was between one and two billion dollars per quarter (depending on the season), with revenue in the third quarter of 2019 at about 2 billion dollars. Advance ticket sales were running just under about two billion per quarter. Since the restart of cruise, advanced ticket sales (which include future cruise credits) have returned to pre-pandemic levels. Cruise line operating expenses were about one billion dollars per quarter and have returned to pre-pandemic levels as the full fleet is back online. During the current quarter revenue has exceeded operating expenses, a positive indicator. Operating income has increased during the current quarter.
In the plot below, quarterly data for revenue, cruise operating expense, marketing general and administrative and net operating profit (NOP) are shown. The dates for operations suspended (March 13, 2020), 1st ship return to service (July 25, 2021) and full fleet operating (May 7 2022) are indicated on the plot with markers.
nop_qrtr = (df_qrtr_data['revenue'].to_numpy() - \
(df_qrtr_data['cruise_operating_expense'].to_numpy() + \
df_qrtr_data['marketing_general_and_administrative'].to_numpy() ))
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('dollars, $B')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['revenue']/1e9,'-+b',label='revenue')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['cruise_operating_expense']/1e9,'-+c',label='cruise operating expense')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['marketing_general_and_administrative']/1e9,'-+m',label='marketing general & administrative')
ax1.plot(df_qrtr_data['QTR'],nop_qrtr/1e9,'-+r',label='NOP')
#ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['advance_ticket_sales']/1e9,'-+g',label='advance ticket sales')
ax1.plot(pd.Timestamp(datetime(2020,3,13)),0,'^k',label='operations suspended, March 13') # March 13, 2020, operations suspended
ax1.plot(pd.Timestamp(datetime(2021,7,25)),0,'ob',label='1st ship return to service, July 25') # July 25, 2021, 1st ship return to service
ax1.plot(pd.Timestamp(datetime(2022,5,7)),0,'>k',label='full fleet operating, May 7') # May 7 2022, full fleet operating
ax1.tick_params(axis='y')
ax1.set_ylim((-1,2))
plt.legend(bbox_to_anchor=(1.1, 1))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('NOP and supporting data')
plt.show()
Net operating profit (NOP) reflects revenue levels and expense requirements of the operating business.
\(\text{NOPM} = \frac{\text{Revenue} - \text{Expenses}}{\text{Revenue}}\)
\(\text{Expenses} = \text{Cost of Goods Sold (CGS)} + \text{General and Administrative (G&A)} + \text{Research and Development (R&D)}\)
General and Administrative (G&A) is also called Sales, General and Administrative (SG&A)
NOP is used to analyze the performance of a company’s core operations without the costs of the capital structure and tax expenses impacting profit. It is a more concise measure of corporate performance since it is able to show earnings before the influence of accounting and financial deductions, also used above in the DCF model. NOP as defined here is roughly equivalent to EBIT (earnings before interest and taxes). What is concerning is that NOP has remained flat at a level of 0.7 billion dollars per quarter for the last four quarters and has not trended up with increasing revenue.
The following chart shows percentage change in quarterly consolidated statement of income, looking back over the last ten quarters or so. This can be used to investigate recient trends.
\(\large{\color{red}{\text{update this section as analysis of balance sheet}}}\)
percent change along with values
fix colors, make the same b,g,r,c,m,y,k
# Set the locator
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
# figsize() function to adjust the size
plt.subplots(1,2,figsize=(15, 5))
# set look back range, left_qtr is the index into the date range
left_qtr = -6
# using subplot function and creating two side by side plots
# plot one
plt.subplot(1, 2, 1)
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['revenue'][left_qtr:]/1e9,'-+b',label='revenue')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['cruise_operating_expense'][left_qtr:]/1e9,'-+g',label='cruise operating expense')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['marketing_general_and_administrative'][left_qtr:]/1e9,'-+r',label='marketing general & administrative')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['other_operating_expense'][left_qtr:]/1e9,'-+c',label='other_operating_expense')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['operating_income'][left_qtr:]/1e9,'-+m',label='operating income')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
#plt.gca().set_xbound(year_ended_list[left_qtr], year_ended_list[-1])
#plt.gca().set_xbound(df_qrtr_data['QTR'].iloc[left_qtr], df_qrtr_data['QTR'].iloc[-1])
#plt.ylim((-100,500))
plt.title('Recent quarterly income items')
ax1.set_ylabel('dollars, $B')
plt.legend()
plt.grid()
# using subplot function and creating plot two
plt.subplot(1, 2, 2)
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['revenue'][left_qtr:]-df_qrtr_data['revenue'].iloc[left_qtr])/np.abs(df_qrtr_data['revenue'].iloc[left_qtr])*1,
'^-b',label='revenue/100')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['cruise_operating_expense'][left_qtr:]-df_qrtr_data['cruise_operating_expense'].iloc[left_qtr])/np.abs(df_qrtr_data['cruise_operating_expense'].iloc[left_qtr])*10,
'^-g',label='cruise operating expense/10')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['marketing_general_and_administrative'][left_qtr:]-df_qrtr_data['marketing_general_and_administrative'].iloc[left_qtr])/np.abs(df_qrtr_data['marketing_general_and_administrative'].iloc[left_qtr])*100,
'^-r',label='marketing general & administrative')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['other_operating_expense'][left_qtr:]-df_qrtr_data['other_operating_expense'].iloc[left_qtr])/np.abs(df_qrtr_data['other_operating_expense'].iloc[left_qtr])*100,
'^-c',label='other operating expense')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['operating_income'][left_qtr:]-df_qrtr_data['operating_income'].iloc[left_qtr])/np.abs(df_qrtr_data['operating_income'].iloc[left_qtr])*100,
'^-m',label='operating income')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Percentage change in quarterly income')
plt.ylabel('percent change')
plt.legend()
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
The plot above scales revenue by 100 and cruise operating expense by 10 inorder to get the plots within a simular range. Revenue, cruise operating expense, marketing general & administrative and other operating expense have been increasing over the period as NCL has resumed operations. In the last two quarters operating income reversed its negative trend.
The following chart shows percentage change in quarterly consolidated balance sheet, looking back over the last ten quarters or so. The first graph show the current assets and liabilities.
\(\large{\color{red}{\text{plot current along side of total assets and liabilities}}}\)
\(\large{\color{red}{\text{organized balance sheet analysis into assets and liabilities, long and short, percent change and magnitude}}}\)
Seems like advance ticket sales could be driving quick ratio.
Think about removing advance ticket sales from debt calculations.
# Set the locator
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
# figsize() function to adjust the size
plt.subplots(1,2,figsize=(15, 5))
# set look back range, left_qtr is the index into the date range
left_qtr = -6
# using subplot function and creating two side by side plots
# plot one
plt.subplot(1, 2, 1)
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_current_assets'][left_qtr:]-df_qrtr_data['total_current_assets'].iloc[left_qtr])/np.abs(df_qrtr_data['total_current_assets'].iloc[left_qtr])*100,
'^-g',label='total_current_assets')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_current_liabilities'][left_qtr:]-df_qrtr_data['total_current_liabilities'].iloc[left_qtr])/np.abs(df_qrtr_data['total_current_liabilities'].iloc[left_qtr])*100,
'^-r',label='total_current_liabilities')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
#plt.gca().set_xbound(year_ended_list[left_qtr], year_ended_list[-1])
#plt.gca().set_xbound(df_qrtr_data['QTR'].iloc[left_qtr], df_qrtr_data['QTR'].iloc[-1])
#plt.ylim((0,20))
plt.title('Percentage change in current assets and liabilities')
plt.ylabel('percent change')
plt.legend()
plt.grid()
# using subplot function and creating plot two
plt.subplot(1, 2, 2)
# color sequence b,g,r,c,m,y,k
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_assets'][left_qtr:]-df_qrtr_data['total_assets'].iloc[left_qtr])/np.abs(df_qrtr_data['total_assets'].iloc[left_qtr])*100,
'^-g',label='total assets')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_liabilities'][left_qtr:]-df_qrtr_data['total_liabilities'].iloc[left_qtr])/np.abs(df_qrtr_data['total_liabilities'].iloc[left_qtr])*100,
'^-r',label='total_liabilities')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Percentage change in total assets and liabilities')
plt.ylabel('percent change')
plt.legend()
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
Current liabilites has been increasing over the past 9 quarters while the current assets have declined. This is probably due to NCL spening its cash reserves as it resumes operations.
The graph below shows recient history of the current assets over the past few quarters. The data is displayed in dollars and relative change.
# Set the locator
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
# figsize() function to adjust the size
plt.subplots(1,2,figsize=(15, 5))
# set look back range, left_qtr is the index into the date range
left_qtr = -6
# using subplot function and creating two side by side plots
# plot one
plt.subplot(1, 2, 1)
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['cash_and_cash_equivalents'][left_qtr:]/1e9,'-+b',label='cash_and_cash_equivalents')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['accounts_receivable'][left_qtr:]/1e9,'-+g',label='accounts_receivable')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['inventories'][left_qtr:]/1e9,'-+r',label='inventories')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['prepaid_expenses_and_other_assets'][left_qtr:]/1e9,'-+c',label='prepaid_expenses_and_other_assets')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['total_current_assets'][left_qtr:]/1e9,'-+m',label='total_current_assets')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
#plt.gca().set_xbound(year_ended_list[left_qtr], year_ended_list[-1])
#plt.gca().set_xbound(df_qrtr_data['QTR'].iloc[left_qtr], df_qrtr_data['QTR'].iloc[-1])
#plt.ylim((-100,500))
plt.title('Recent quarterly current assets')
plt.ylabel('dollars, $B')
plt.legend()
plt.grid()
# using subplot function and creating plot two
plt.subplot(1, 2, 2)
# color sequence b,g,r,c,m,y,k
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['cash_and_cash_equivalents'][left_qtr:]-df_qrtr_data['cash_and_cash_equivalents'].iloc[left_qtr])/np.abs(df_qrtr_data['cash_and_cash_equivalents'].iloc[left_qtr])*100,
'^-b',label='cash & cash equivalents')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['accounts_receivable'][left_qtr:]-df_qrtr_data['accounts_receivable'].iloc[left_qtr])/np.abs(df_qrtr_data['accounts_receivable'].iloc[left_qtr])*10,
'^-g',label='accounts receivable/10')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['inventories'][left_qtr:]-df_qrtr_data['inventories'].iloc[left_qtr])/np.abs(df_qrtr_data['inventories'].iloc[left_qtr])*100,
'^-r',label='inventories')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['prepaid_expenses_and_other_assets'][left_qtr:]-df_qrtr_data['prepaid_expenses_and_other_assets'].iloc[left_qtr])/np.abs(df_qrtr_data['prepaid_expenses_and_other_assets'].iloc[left_qtr])*100,
'^-c',label='prepaid_expenses_and_other_assets')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_current_assets'][left_qtr:]-df_qrtr_data['total_current_assets'].iloc[left_qtr])/np.abs(df_qrtr_data['total_current_assets'].iloc[left_qtr])*100,
'^-m',label='total_current_assets')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Percentage change in current assets')
plt.ylabel('percent change')
plt.legend()
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
In the plot above, accounts receivable has been scalled by 10. As can be seen, cash & cash equivalents has declined over the period as NCL is spending money to sustain the fleet and resume operations.
The graph below shows recient history of the current liabilities over the past few quarters. The data is displayed in dollars and relative change.
# Set the locator
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
# figsize() function to adjust the size
plt.subplots(1,2,figsize=(15, 5))
# set look back range, left_qtr is the index into the date range
left_qtr = -6
# using subplot function and creating two side by side plots
# plot one
plt.subplot(1, 2, 1)
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['current_portion_of_long_term_debt'][left_qtr:]/1e9,'-+b',label='current_portion_of_long_term_debt')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['accounts_payable'][left_qtr:]/1e9,'-+g',label='accounts_payable')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['accrued_expenses_and_other_liabilities'][left_qtr:]/1e9,'-+r',label='accrued_expenses_and_other_liabilities')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['advance_ticket_sales'][left_qtr:]/1e9,'-+c',label='advance_ticket_sales')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['total_current_liabilities'][left_qtr:]/1e9,'-+m',label='total_current_liabilities')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
#plt.gca().set_xbound(year_ended_list[left_qtr], year_ended_list[-1])
#plt.gca().set_xbound(df_qrtr_data['QTR'].iloc[left_qtr], df_qrtr_data['QTR'].iloc[-1])
#plt.ylim((-100,500))
plt.title('Recent quarterly current liabilities')
plt.ylabel('dollars, $B')
plt.legend()
plt.grid()
# using subplot function and creating plot two
plt.subplot(1, 2, 2)
# color sequence b,g,r,c,m,y,k
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['current_portion_of_long_term_debt'][left_qtr:]-df_qrtr_data['current_portion_of_long_term_debt'].iloc[left_qtr])/np.abs(df_qrtr_data['current_portion_of_long_term_debt'].iloc[left_qtr])*100,
'^-b',label='current_portion_of_long_term_debt')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['accounts_payable'][left_qtr:]-df_qrtr_data['accounts_payable'].iloc[left_qtr])/np.abs(df_qrtr_data['accounts_payable'].iloc[left_qtr])*100,
'^-g',label='accounts_payable')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['accrued_expenses_and_other_liabilities'][left_qtr:]-df_qrtr_data['accrued_expenses_and_other_liabilities'].iloc[left_qtr])/np.abs(df_qrtr_data['accrued_expenses_and_other_liabilities'].iloc[left_qtr])*100,
'^-r',label='accrued_expenses_and_other_liabilities')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['advance_ticket_sales'][left_qtr:]-df_qrtr_data['advance_ticket_sales'].iloc[left_qtr])/np.abs(df_qrtr_data['advance_ticket_sales'].iloc[left_qtr])*100,
'^-',label='advance_ticket_sales')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_current_liabilities'][left_qtr:]-df_qrtr_data['total_current_liabilities'].iloc[left_qtr])/np.abs(df_qrtr_data['total_current_liabilities'].iloc[left_qtr])*100,
'^-c',label='total_current_liabilities')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Percentage change in current liabilities')
plt.ylabel('percent change')
plt.legend()
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
The plot above shows the change in total assests, long term debt and total liabilities. Of consern is that long term debt and total liabilities have been increasing whereas total assets have not.
Cash and cash equalivents have been declining, while advance_ticket_sales has also been increasing so these work against each other to make the ratio look bad.
\(\large{\color{red}{\text{research this}}}\)
What are the balance sheet factors that contribute and what does the 10Q say about them?
\(\large{\color{red}{\text{comment}}}\)
Goodwill and other indefinite-lived assets, principally tradenames, are reviewed for impairment on an annual basis or earlier if there is an event or change in circumstances that would indicate that the carrying value of these assets may not be fully recoverable. We believe our estimates and judgments with respect to our long-lived assets, principally ships, and goodwill and other indefinite-lived intangible assets are reasonable. Nonetheless, if there was a material change in assumptions used in the determination of such fair values or if there is a material change in the conditions or circumstances that influence such assets, we could be required to record an impairment charge. We estimate fair value based on the best information available utilizing estimates, judgments and projections as necessary. As of December 31, 2021, our annual review supports the carrying value of these assets.
Trade names were $500.5 million as of December 31, 2021. As of December 31, 2021, our annual impairment reviews support the carrying values of these assets.
Other long-term assets Operating leases Right-of-use assets Fuel contracts
# Set the locator
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
# figsize() function to adjust the size
plt.subplots(1,2,figsize=(15, 5))
# set look back range, left_qtr is the index into the date range
left_qtr = -6
# using subplot function and creating two side by side plots
# plot one
plt.subplot(1, 2, 1)
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['total_current_assets'][left_qtr:]/1e9,'-+b',label='total_current_assets')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['property_and_equipment'][left_qtr:]/1e9,'-+g',label='property_and_equipment')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['goodwill'][left_qtr:]/1e9,'-.k',label='goodwill')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['trade_names'][left_qtr:]/1e9,'-.y',label='trade_names')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['other_long_term_assets'][left_qtr:]/1e9,'-+r',label='other_long_term_assets')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['total_assets'][left_qtr:]/1e9,'-+c',label='total_assets')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
#plt.gca().set_xbound(year_ended_list[left_qtr], year_ended_list[-1])
#plt.gca().set_xbound(df_qrtr_data['QTR'].iloc[left_qtr], df_qrtr_data['QTR'].iloc[-1])
plt.ylim((0,20))
plt.title('Recent quarterly current assets')
plt.ylabel('dollars, $B')
plt.legend()
plt.grid()
# using subplot function and creating plot two
plt.subplot(1, 2, 2)
# color sequence b,g,r,c,m,y,k
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_current_assets'][left_qtr:]-df_qrtr_data['total_current_assets'].iloc[left_qtr])/np.abs(df_qrtr_data['total_current_assets'].iloc[left_qtr])*100,
'^-b',label='total_current_assets')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['property_and_equipment'][left_qtr:]-df_qrtr_data['property_and_equipment'].iloc[left_qtr])/np.abs(df_qrtr_data['property_and_equipment'].iloc[left_qtr])*100,
'^-g',label='property_and_equipment')
#ax1.plot(df_qrtr_data['QTR'][left_qtr:],
# (df_qrtr_data['goodwill'][left_qtr:]-df_qrtr_data['goodwill'].iloc[left_qtr])/np.abs(df_qrtr_data['goodwill'].iloc[left_qtr])*100,
# '^-',label='goodwill')
#ax1.plot(df_qrtr_data['QTR'][left_qtr:],
# (df_qrtr_data['trade_names'][left_qtr:]-df_qrtr_data['trade_names'].iloc[left_qtr])/np.abs(df_qrtr_data['trade_names'].iloc[left_qtr])*100,
# '^-',label='trade_names')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['other_long_term_assets'][left_qtr:]-df_qrtr_data['other_long_term_assets'].iloc[left_qtr])/np.abs(df_qrtr_data['other_long_term_assets'].iloc[left_qtr])*100,
'^-r',label='other_long_term_assets')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_assets'][left_qtr:]-df_qrtr_data['total_assets'].iloc[left_qtr])/np.abs(df_qrtr_data['total_assets'].iloc[left_qtr])*100,
'^-c',label='total assets')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Percentage change in assets')
plt.ylabel('percent change')
plt.legend()
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
# Set the locator
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
# figsize() function to adjust the size
plt.subplots(1,2,figsize=(15, 5))
# set look back range, left_qtr is the index into the date range
left_qtr = -6
# using subplot function and creating two side by side plots
# plot one
plt.subplot(1, 2, 1)
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['total_current_liabilities'][left_qtr:]/1e9,'-+b',label='total_current_liabilities')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['long_term_debt'][left_qtr:]/1e9,'-+g',label='long_term_debt')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['other_long_term_liabilities'][left_qtr:]/1e9,'-+r',label='other_long_term_liabilities')
plt.plot(df_qrtr_data['QTR'][left_qtr:],df_qrtr_data['total_liabilities'][left_qtr:]/1e9,'-+c',label='total_liabilities')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
# Changes x-axis range
#plt.gca().set_xbound(year_ended_list[left_qtr], year_ended_list[-1])
#plt.gca().set_xbound(df_qrtr_data['QTR'].iloc[left_qtr], df_qrtr_data['QTR'].iloc[-1])
plt.ylim((0,20))
plt.title('Recent quarterly current Liabilities')
plt.ylabel('dollars, $B')
plt.legend()
plt.grid()
# using subplot function and creating plot two
plt.subplot(1, 2, 2)
# color sequence b,g,r,c,m,y,k
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_current_liabilities'][left_qtr:]-df_qrtr_data['total_current_liabilities'].iloc[left_qtr])/np.abs(df_qrtr_data['total_current_liabilities'].iloc[left_qtr])*100,
'^-b',label='total_current_liabilities')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['long_term_debt'][left_qtr:]-df_qrtr_data['long_term_debt'].iloc[left_qtr])/np.abs(df_qrtr_data['long_term_debt'].iloc[left_qtr])*100,
'^-g',label='long term debt')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['other_long_term_liabilities'][left_qtr:]-df_qrtr_data['other_long_term_liabilities'].iloc[left_qtr])/np.abs(df_qrtr_data['other_long_term_liabilities'].iloc[left_qtr])*100,
'^-r',label='long other_long_term_liabilities debt')
plt.plot(df_qrtr_data['QTR'][left_qtr:],
(df_qrtr_data['total_liabilities'][left_qtr:]-df_qrtr_data['total_liabilities'].iloc[left_qtr])/np.abs(df_qrtr_data['total_liabilities'].iloc[left_qtr])*100,
'^-c',label='total liabilities')
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Percentage change in Liabilities')
plt.ylabel('percent change')
plt.legend()
plt.grid()
# space between the plots
#plt.tight_layout(4)
# show plot
plt.show()
\(\large{\color{red}{\text{update this section, remove principal payments}}}\)
The current long term amounts were obtained from the quarterly reports. Since March 2020, Moody’s has downgraded NCLH’s long-term issuer rating to B2, the senior secured rating to B1 and the senior unsecured rating to Caa1. Since April 2020, S&P Global has downgraded the issuer credit rating to B, lowered the issue-level rating on the \$875 million Revolving Loan Facility and \$1.5 billion Term Loan A Facility to BB-, the issue-level rating on the other senior secured notes to B+ and the senior unsecured rating to B-. If the credit ratings were to be further downgraded, or general market conditions were to ascribe higher risk to NCLH’s rating levels, the cruise industry, NCLH’s access to capital and the cost of any debt or equity financing will be further negatively impacted. NCLH also has significant capacity to incur additional indebtedness under the debt agreements and may issue additional ordinary shares from time to time, subject to the authorized number of ordinary shares.
NCLH may be required to pledge additional collateral and/or post additional cash reserves or take other actions that may reduce the liquidity.
The Principal Payout Schedule was obtained from a pdf file on the NCLH investor web page and a new dataframe was created for this data.
Principal Payout Schedule (in U.S. dollars, thousands) As of September 30, 2022
| Date | Debt |
|---|---|
| Q4 2022 | 331,440 |
| 2023 | 934,497 |
| 2024 | 3,683,554 |
| 2025 | 1,067,735 |
| 2026 | 1,971,347 |
| 2027 | 3,022,242 |
df_principal_payout_schedule = pd.DataFrame(data={ ‘date’:np.array([‘2022-12-31’,‘2023-12-31’,‘2024-12-31’,‘2025-12-31’,‘2026-12-31’,‘2027-12-31’]).astype(‘datetime64[D]’), ‘amount’:-np.array([-331440,-934497,-3683554,-1067735,-1971347,-3022242])*1000 })
df_principal_payout_schedule = pd.DataFrame(data={ ‘date’:np.array([‘2022-12-31’,‘2023-03-31’,‘2023-06-30’,‘2023-09-30’,‘2023-12-31’,‘2024-03-31’,‘2024-06-30’,‘2024-09-30’,‘2024-12-31’, ‘2025-03-31’,‘2025-06-30’,‘2025-09-30’,‘2025-12-31’,‘2026-03-31’,‘2026-06-30’,‘2026-09-30’,‘2026-12-31’,‘2027-03-31’, ‘2027-06-30’,‘2027-09-30’,‘2027-12-31’]).astype(‘datetime64[D]’), ‘amount’:-np.array([-331440,-249608.5,-249608.5,-249608.5,-249608.5,-940259.75,-940259.75,-940259.75,-940259.75,-285862.25, -285862.25,-285862.25,-285862.25,-511711.25,-511711.25,-511711.25,-511711.25,-774823.75,-774823.75, -774823.75,-774823.75])*1000 })
The plot below shows the past repayments of long term debt and the long term debt level at the end of each quarter since 2019. Also shown are the future principal payout amounts for the remainder of 2022 to 2018. Presumably, in years 2024 to 2028, quarterly payments would be required and not just the end of year amounts.
NCLH has received financial and other debt covenant waivers and added new free liquidity requirements. As of December 31, 2021, taking into account such waivers, NCLH is in compliance with all debt covenants.
If NCLH does not remain in compliance with the covenants, they would have to seek to amend the covenants. However, no assurances can be made that such amendments would be approved by the lenders. Generally, if an event of default under any debt agreement occurs, then pursuant to cross default and/or cross acceleration clauses, substantially all of our outstanding debt and derivative contract payables could become due, and all debt and derivative contracts could be terminated, which would have a material adverse impact to our operations and liquidity.
NCLH recognizes the risk of needing additional financing or financing to optimize the balance sheet, which may not be available on favorable terms, or at all, and the outstanding exchangeable notes and any future financing which may be dilutive to existing shareholders.
\(\large{\color{red}{\text{review increase in debt, and decrease in equity}}}\)
\(\large{\color{red}{\text{add amounts and dates when new debt was incured}}}\)
locator = mdates.AutoDateLocator() #locator = mdates.MonthLocator((1,4,7,10)) #locator = mdates.MonthLocator((3,6,9,12)) #fmt = mdates.DateFormatter(‘%b %Y’) fmt = mdates.DateFormatter(‘%Y’)
fig, ax1 = plt.subplots() ax1.set_ylabel(‘dollars, $B’)
plt.bar(df_principal_payout_schedule[‘date’],df_principal_payout_schedule[‘amount’]/1e9, width=50,label=‘principal payout schedule’) plt.bar(df_qrtr_data[‘QTR’],-df_qrtr_data[‘repayments_of_long_term_debt’]/1e9, width=50,label=‘repayments of long term debt’) plt.plot(df_qrtr_data[‘QTR’],df_qrtr_data[‘long_term_debt’]/1e9,‘^’ ,label=‘long term debt’)
ax1.tick_params(axis=‘y’) #ax1.set_ylim((0,20)) plt.legend(bbox_to_anchor=(1.6, 1)) #ax1.legend() plt.grid()
X = plt.gca().xaxis X.set_major_locator(locator) # Specify formatter X.set_major_formatter(fmt) plt.gcf().autofmt_xdate()
plt.title(‘Long term debt and payments’) plt.show()
print(‘total principal payments due: ${:.2f}B’.format(df_principal_payout_schedule[‘amount’].sum()/1e9))
The plot above shows that NCLH has significant debt payments coming due and must generate sufficient cash flow to make these payments while paying for operations.
As of September 30, 2022, material cash requirements for debt and ship construction were as follows (in thousands):
| Material Cash Requirements | Remainder of 2022 | 2023 | 2024 | 2025 | 2026 | 2027 |
|---|---|---|---|---|---|---|
| Long-term debt (1) | 446,559 | 1,610,548 | 4,202,925 | 1,527,174 | 2,338,005 | 3,283,685 |
| Ship construction contracts (2) | 25,047 | 2,178,120 | 249,655 | 1,463,558 | 1,648,375 | 761,282 |
| Total | 471,606 | 3,788,668 | 4,452,580 | 2,990,732 | 3,986,380 | 4,044,967 |
Includes principal as well as estimated interest payments with LIBOR held constant as of September 30, 2022. Includes exchangeable notes which can be settled in shares. Excludes the impact of any future possible refinancings and undrawn export-credit backed facilities.
Ship construction contracts are for newbuild ships based on the euro/U.S. dollar exchange rate as of September 30, 2022. As of September 30, 2022, NCLH has committed undrawn export-credit backed facilities of approximately \$5.4 billion which funds approximately 80% of ship construction contracts.
After giving effect to delays in certain scheduled ship delivery dates revised subsequent to September 30, 2022, material cash requirements for ship construction contracts, given annually in the 2022 3rd quarter 10Q. Annual amounts were evenly divided and apportioned to each quarter.
\(\large{\color{red}{\text{these amounts are different, why?}}}\)
The principal_payout_schedule needs to include ???
df_material_cash_requirements = pd.DataFrame(data={
'date':np.array(['2022-12-31','2023-03-31','2023-06-30','2023-09-30','2023-12-31','2024-03-31','2024-06-30','2024-09-30','2024-12-31',
'2025-03-31','2025-06-30','2025-09-30','2025-12-31','2026-03-31','2026-06-30','2026-09-30','2026-12-31','2027-03-31',
'2027-06-30','2027-09-30','2027-12-31']).astype('datetime64[D]'),
'long term debt':np.array([446559,402637,402637,402637,402637,1050731.25,1050731.25,1050731.25,1050731.25,381793.5,
381793.5,381793.5,381793.5,584501.25,584501.25,584501.25,584501.25,820921.25,820921.25,
820921.25,820921.25])*1000
})df_material_cash_requirements = pd.DataFrame(data={ ‘date’:np.array([‘2022-12-31’,‘2023-03-31’,‘2023-06-30’,‘2023-09-30’,‘2023-12-31’,‘2024-03-31’,‘2024-06-30’,‘2024-09-30’,‘2024-12-31’, ‘2025-03-31’,‘2025-06-30’,‘2025-09-30’,‘2025-12-31’,‘2026-03-31’,‘2026-06-30’,‘2026-09-30’,‘2026-12-31’,‘2027-03-31’, ‘2027-06-30’,‘2027-09-30’,‘2027-12-31’]).astype(‘datetime64[D]’), ‘long term debt’:np.array([446559,402637,402637,402637,402637,1050731.25,1050731.25,1050731.25,1050731.25,381793.5, 381793.5,381793.5,381793.5,584501.25,584501.25,584501.25,584501.25,820921.25,820921.25, 820921.25,820921.25])1000, ‘ship construction contracts’:np.array([25047,544530,544530,544530,544530,62413.75,62413.75,62413.75,62413.75,365889.5, 365889.5,365889.5,365889.5,412093.75,412093.75,412093.75,412093.75,190320.5, 190320.5,190320.5,190320.5])1000
})
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots(figsize=(8, 4))
ax1.set_ylabel('Dollars, $B')
# plot Material Cash Requirements
ax1.bar(df_material_cash_requirements['date'],df_material_cash_requirements['long term debt']/1e9, width=50,label='long term debt, principal+interest payments')
#ax1.bar(df_material_cash_requirements['date'],df_material_cash_requirements['ship construction contracts']/1e9, width=50,
# bottom=df_material_cash_requirements['long term debt']/1e9,label='ship construction')
ax1.tick_params(axis='y')
#ax1.set_ylim((0,14))
#ax1.legend(bbox_to_anchor=(1.1, 1))
plt.grid()
# instantiate a second y-axes that shares the same x-axis
#ax2 = ax1.twinx()
#color = 'tab:green'
#ax2.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_assets']/df_qrtr_data['total_current_liabilities'],'-.g',label='current ratio')
#ax2.set_ylabel('current ratio',color=color)
#ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((0,3))
#ax2.legend(bbox_to_anchor=(1.45, 0))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Future principal & interest payments, by quarter')
plt.show()
The plot above shows the future future principal & interest payments, by quarter. Over 4 billion dollars are due in 2024.
The plot below shows historical repayments of long term debt, long term debt, cash & cash equivalents and future principal & interest payments.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
#fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots(figsize=(15, 4))
ax1.set_ylabel('dollars, $B')
#plt.bar(df_principal_payout_schedule['date'],df_principal_payout_schedule['amount']/1e9, width=50,label='principal payout schedule')
plt.bar(df_qrtr_data['QTR'],-df_qrtr_data['repayments_of_long_term_debt']/1e9, width=20,label='repayments of long term debt')
ax1.bar(df_material_cash_requirements['date'],df_material_cash_requirements['long term debt']/1e9, width=20,label='long term debt, principal+interest payments')
plt.plot(df_qrtr_data['QTR'],df_qrtr_data['long_term_debt']/1e9,'^' ,label='long term debt')
plt.plot(df_qrtr_data['QTR'],df_qrtr_data['cash_and_cash_equivalents']/1e9,'-g' ,label='cash & cash equivalents')
ax1.tick_params(axis='y')
#ax1.set_ylim((0,15))
ax1.yaxis.set_ticks(np.arange(0, 15, 1))
plt.legend(bbox_to_anchor=(1, 1))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Long term debt and payments')
plt.show()
The level of long term debt is plotted at each quarter as blue triangles. The level of long term debt has been increasing over the past four quarters. The historical repayment of long term debt is shown as blue bars. The historical amount of cash & cash equivalents for each quarter is shown as the green line in the plot. The level of debt, the cash on hand and the amounts of future debt payments are examined below. NCL will need to generate sufficient cash eqch quarter to fund operations and make debt payments. The cash flow model below shows that this will be a chalange.
The debt to equity (D/E) ratio is used to evaluate a company’s financial leverage and is calculated by dividing a company’s total liabilities by its shareholder equity. Higher ratios tend to indicate a company with higher risk to shareholders. When using the D/E ratio, it is very important to consider the industry in which the company operates. Because different industries have different capital needs and growth rates, a relatively high D/E ratio may be common in one industry, while a relatively low D/E may be common in another. Generally speaking, a D/E ratio below 1.0 would be seen as relatively safe, whereas ratios of 2.0 or higher would be considered risky.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Dollars, $B, or ratio')
# plot revenue, current assets and liabilities
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['revenue']/1e9,'-+',label='revenue')
#ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_assets']/1e9,'-+m',label='total assets')
#ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_liabilities']/1e9,'-+c',label='total liabilities')
ax1.plot(df_qrtr_data['QTR'],(df_qrtr_data['total_assets']-df_qrtr_data['total_liabilities'])/1e9,'-+',label='equity')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['long_term_debt']/(df_qrtr_data['total_assets']-df_qrtr_data['total_liabilities']),'-+',label='D/E')
ax1.plot(pd.Timestamp(datetime(2020,3,13)),0.2,'^k',label='operations suspended, March 13') # March 13, 2020, operations suspended
ax1.plot(pd.Timestamp(datetime(2021,7,25)),0.2,'ob',label='1st ship return to service, July 25') # July 25, 2021, 1st ship return to service
ax1.plot(pd.Timestamp(datetime(2022,5,7)),0.2,'>k',label='full fleet operating, May 7') # May 7 2022, full fleet operating
ax1.tick_params(axis='y')
#ax1.set_ylim((0,14))
ax1.legend(bbox_to_anchor=(1.1, 1))
plt.grid()
# instantiate a second y-axes that shares the same x-axis
#ax2 = ax1.twinx()
#color = 'tab:green'
#ax2.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_assets']/df_qrtr_data['total_current_liabilities'],'-.g',label='current ratio')
#ax2.set_ylabel('current ratio',color=color)
#ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((0,3))
#ax2.legend(bbox_to_anchor=(1.45, 0))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Debt to equity')
plt.show()
The D/E ratio for NCL was historically less than two before the pandemic, however, presently the D/E ratio is above 30. The company’s survival took precedence over maintaining a healthy balance sheet.
Nov 13, 2022 yahoo finance reported cruise line D/E as follows: - Royal Caribbean Cruises Ltd. (RCL): D/E = 7.47 - Carnival Corporation & plc (CCL): D/E = 4.21 - Norwegian Cruise Line Holdings Ltd. (NCLH): D/E = 36.63
The current ratio is a liquidity ratio that measures a company’s ability to pay short-term obligations or those due within one year. In theory, the higher the current ratio, the more capable a company is of paying its obligations because it has a larger proportion of short-term asset value relative to the value of its short-term liabilities. The current ratio can be a useful measure of a company’s short-term solvency when it is placed in the context of what has been historically normal for the company and its peer group. It also offers more insight when calculated repeatedly over several periods.
The plot below shows revenue, current assets, current liabilities and the current ratio. Markers are also shown for the dates when operations were suspended, the first NCL ship returned to service and when the full fleet was back in operation.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Dollars, $B, or current ratio')
# plot revenue, current assets and liabilities
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['revenue']/1e9,'-+b',label='revenue')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_assets']/1e9,'-+m',label='current assets')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_liabilities']/1e9,'-+c',label='current liabilities')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_assets']/df_qrtr_data['total_current_liabilities'],'-.g',label='current ratio')
ax1.plot(pd.Timestamp(datetime(2020,3,13)),0.1,'^k',label='operations suspended, March 13') # March 13, 2020, operations suspended
ax1.plot(pd.Timestamp(datetime(2021,7,25)),0.1,'ob',label='1st ship return to service, July 25') # July 25, 2021, 1st ship return to service
ax1.plot(pd.Timestamp(datetime(2022,5,7)),0.1,'>k',label='full fleet operating, May 7') # May 7 2022, full fleet operating
ax1.tick_params(axis='y')
ax1.set_ylim((0,6))
ax1.legend(bbox_to_anchor=(1.1, 1))
plt.grid()
# instantiate a second y-axes that shares the same x-axis
#ax2 = ax1.twinx()
#color = 'tab:green'
#ax2.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_assets']/df_qrtr_data['total_current_liabilities'],'-.g',label='current ratio')
#ax2.set_ylabel('current ratio',color=color)
#ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((0,3))
#ax2.legend(bbox_to_anchor=(1.45, 0))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Current ratio')
plt.show()
Prior to the pandemic shut down, NCLH was operating with a current ratio of about 0.25, a historical normal level for the company. During the shut down, NCL increased cash holding to sustain the company while cruise operations were suspended and no revenue was being generated. Now that cruise operations have started to ramp up, we see a reversion of the current ratio back to below a ratio of one. Since the company has negative earnings, a ratio below one is of concern, since it indicates the company might not have sufficient cash to sustain operations.
The plot below shows cash & cash equivalents and current liabilities less advance ticket sales. Markers are also shown for the dates when operations were suspended, the first NCLH ship returned to service and when the full fleet was back in operation. An additional marker shows the date of March 31, 2023, when the \$1B commitment available will end. The expiration of the Commitment Facility was extended through March 31, 2023.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Dollars, $B, or ratio')
# plot revenue, current assets and liabilities
#ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['advance_ticket_sales']/1e9,'-+',label='advance ticket sales')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['cash_and_cash_equivalents']/1e9,'-+',label='cash & cash equivalents')
#ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_liabilities']/1e9,'-+c',label='current liabilities')
ax1.plot(df_qrtr_data['QTR'],(df_qrtr_data['total_current_liabilities']-df_qrtr_data['advance_ticket_sales'])/1e9,'-+m',label='current liabilities less advance ticket sales')
#ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_assets']/1e9,'-+',label='current assets')
ax1.plot(pd.Timestamp(datetime(2020,3,13)),0.1,'^k',label='operations suspended, March 13') # March 13, 2020, operations suspended
ax1.plot(pd.Timestamp(datetime(2021,7,25)),0.1,'ob',label='1st ship return to service, July 25') # July 25, 2021, 1st ship return to service
ax1.plot(pd.Timestamp(datetime(2022,5,7)),0.1,'>k',label='full fleet operating, May 7') # May 7 2022, full fleet operating
ax1.plot(pd.Timestamp(datetime(2023,3,31)),1.0,'*r',label='$1B commitment available ending, Mar 31') # March 31, 2023, $1B commitment available ending
ax1.tick_params(axis='y')
ax1.set_ylim((0,4))
ax1.legend(bbox_to_anchor=(1.1, 1))
plt.grid()
# instantiate a second y-axes that shares the same x-axis
#ax2 = ax1.twinx()
#color = 'tab:green'
#ax2.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_assets']/df_qrtr_data['total_current_liabilities'],'-.g',label='current ratio')
#ax2.set_ylabel('current ratio',color=color)
#ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((0,3))
#ax2.legend(bbox_to_anchor=(1.45, 0))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Liquidity')
plt.show()
One of the components of current liabilities is advance ticket sales, which are deposits made by customers on future cruises or conversion of refunds owed from canceled cruises to future cruise credits. If we think of advance ticket sales as future revenue not yet classified as revenue, we can subtract that amount from current liabilities to get an indication of actual current liabilities.
As of December 31, 2021, NCLH had advance ticket sales of \$1.8 billion, including the long-term portion, which included approximately \$0.7 billion of future cruise credits. NCLH also has agreements with credit card processors that, as of December 31, 2021, governed approximately \$1.3 billion in advance ticket sales that had been received by the Company relating to future voyages. These agreements allow the credit card processors to require under certain circumstances, including the existence of a material adverse change, excessive chargebacks and other triggering events, that the Company maintain a reserve which would be satisfied by posting collateral.
Although the agreements vary, these requirements may generally be satisfied either through a percentage of customer payments withheld or providing cash funds directly to the card processor. Any cash reserve or collateral requested could be increased or decreased. As of December 31, 2021, NCLH had cash collateral reserves of approximately \$1.2 billion with credit card processors recognized in accounts receivable, net or other long-term assets
Comparing current liabilities less advance ticket sales to cash and cash equivalents, we see that current liabilities less advance ticket sales has recently exceeded cash and cash equivalents, which is of concern.
The book value of a company is the net difference between that company’s total assets and total liabilities, where book value reflects the total value of a company’s assets that shareholders of that company would receive if the company were to be liquidated. It serves as the total value of the company’s assets that shareholders would theoretically receive if a company was liquidated. This assumes that the liquidation value of assets is equal to the value claimed by the company.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Dollars, $B')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_assets']/1e9,'-+',label='total assets')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['total_liabilities']/1e9,'-+',label='total liabilities')
ax1.plot(df_qrtr_data['QTR'],(df_qrtr_data['total_assets']-df_qrtr_data['total_liabilities'])/1e9,'-+',label='book value')
ax1.tick_params(axis='y')
#ax1.set_ylim((0,20))
plt.legend(bbox_to_anchor=(1.6, 1))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Book value')
plt.show()
As shown in the plot above, the difference between total assets and total liabilities has been narrowing over the past ten quarters as NCL has been raising cash with loans and by selling shares. If NCLH takes the \$1 billion dollar commitment, which would increase liabilities by the same amount, the book value of the company would be about \$0.5 billion dollars.
\(\large{\color{red}{\text{update comments about book value}}}\)
print('book value MRQ = ${:.2f}B'.\
format(df_qrtr_data['total_assets'].iloc[-1]/1e9 - (df_qrtr_data['total_liabilities'].iloc[-1]/1e9)))book value MRQ = $0.07Bprint('book value with $1 billion dollar commitment = ${:.2f}B'.\
format(df_qrtr_data['total_assets'].iloc[-1]/1e9 - (df_qrtr_data['total_liabilities'].iloc[-1]/1e9 + 1.0)))book value with $1 billion dollar commitment = $-0.93BThe weighted average of outstanding shares is a calculation that incorporates any changes in the number of a company’s outstanding shares over a reporting period. The reporting period usually coincides with a company’s quarterly or annual reports. NCL calculates the weighted average of outstanding shares and reports the number. Shares outstanding refers to the amount of stock held by shareholders, including restrictive shares held by company insiders. The plot below shows the number of weighted average of outstanding shares each quarter.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('basic shares outstanding, M')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['weighted_average_shares_outstanding_basic']/1e6,'-+',label='weighted_average_shares_outstanding_basic, M')
ax1.tick_params(axis='y')
#ax1.set_ylim((0,20))
#plt.legend(bbox_to_anchor=(1.6, 1))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Weighted average shares outstanding basic')
plt.show()
The plot above shows that weighted average shares outstanding basic at the end of each quarter. NCLH has been raising cash by selling shares and as can be seen, there are almost double the number of shares outstanding.
Book value per share is a method to calculate the per-share book value of a company based on common shareholders’ equity in the company. Should the company dissolve, the book value per common share indicates the dollar value remaining for common shareholders after all assets are liquidated and all debtors are paid. A company’s share price usually trades at many multiples of the book value, so book value can serve as a reference point. A declining book value would be a red flag for an investor.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Dollars')
ax1.plot(df_qrtr_data['QTR'],(df_qrtr_data['total_assets']-df_qrtr_data['total_liabilities'])/df_qrtr_data['weighted_average_shares_outstanding_basic'],'-+',label='book value per share')
ax1.tick_params(axis='y')
ax1.set_ylim((0,35))
#plt.legend(bbox_to_anchor=(1.6, 1))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Book value per weighted average share')
plt.show()
print('book value per weighted average share, MRQ = ${:.2f}'.\
format((df_qrtr_data['total_assets'].iloc[-1]-df_qrtr_data['total_liabilities'].iloc[-1])/df_qrtr_data['weighted_average_shares_outstanding_basic'].iloc[-1]))
book value per weighted average share, MRQ = $0.16The Book value per weighted average share at the end of the second quarter of 2022 was less than \$5 dollars. This measure of value indicates that investors have had their investments diluted by about a factor of 10 compared to pre pandemic share prices. The current share price reflects some optimism in the ability of the company to regain at least some positive cash flow in the near future.
November 13, 2022 yahoo finance reported cruise line Book Value per Share (BVS) as follows: - Royal Caribbean Cruises Ltd. (RCL): BVS = \$12.56 - Carnival Corporation & plc (CCL): BVS = \$6.66 - Norwegian Cruise Line Holdings Ltd. (NCLH): BVS = \$0.95
NCLH generates the majority of revenue from ticket sales. NCLH has stated that NCLH will not discount tickets to fill their ships as this would hurt the brand.
Booking trends for full year 2023 remain positive with cumulative booked position in line with a record 2019 inclusive of the Company’s 20% increase in capacity. Pricing continues to be significantly higher than that of 2019 at a similar point in time and thus at record levels for full year 2023.
The future cruise credits are not contracts, and therefore, guests who elected this option are excluded from our contract liability balance; however, the credit for the original amount paid is included in advance ticket sales.
The future cruise credits issued under these programs are generally valid for any sailing through December 31, 2022, and we may extend the length of time these future cruise credits may be redeemed. The use of such credits may prevent us from garnering certain future cash collections as staterooms booked by guests with such credits will not be available for sale, resulting in less cash collected from bookings to new guests. We may incur incremental commission expense for the use of these future cruise credits.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('dollars, $B')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['advance_ticket_sales']/1e9,'-+g',label='advance ticket sales')
ax1.plot(df_qrtr_data['QTR'],df_qrtr_data['future_cruise_credits']/1e9,'-+b',label='future cruise credits')
ax1.plot(df_qrtr_data['QTR'],(df_qrtr_data['advance_ticket_sales']-df_qrtr_data['future_cruise_credits'])/1e9,'-.',label='ticket sales less future cruise credits')
ax1.plot(pd.Timestamp(datetime(2020,3,13)),0,'^k',label='operations suspended, March 13') # March 13, 2020, operations suspended
ax1.plot(pd.Timestamp(datetime(2021,7,25)),0,'ob',label='1st ship return to service, July 25') # July 25, 2021, 1st ship return to service
ax1.plot(pd.Timestamp(datetime(2022,5,7)),0,'>k',label='full fleet operating, May 7') # May 7 2022, full fleet operating
ax1.tick_params(axis='y')
ax1.set_ylim((-0.1,2.5))
plt.legend(bbox_to_anchor=(1.7, 1))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Ticket sales')
plt.show()
Any value in looking at Passengers carried, Passenger Cruise Days, berths and Capacity Days?
Why the difference between advanced ticket sales and ticket sales less FCC? Seems like during the pause, all ticket sales should have been converted to FCC. Ticket sales less future cruise credits should be new bookings, which are almost \$2B.
Dilution from value-add FCCs issued during the pandemic will not carry over into 2023 as the bonus portion of these FCCs expire at YE2022. (from slide 13 of Third Quarter 2022 Earnings Conference Call, November 8, 2022)
A liquidity model was developed to estimate future cash on hand and ability to pay future debt. The liquidity model starts with a projected occupancy profile and number of berths available. Revenue is calculated from estimated passenger cruise days. Operating expense is estimated from capacity days. Future cash flow is estimated from revenue less operating expenses and marketing, general and administrative expenses. Liquidity is the running total of future cash flows (which might be negative) and the present cash and cash equivalents less debt payments.
The calculations for future liquidity follow these steps: - estimate future occupancy percentage, currently at 80% and rising to final value of 107% - data for future berths - estimate capacity days - estimate passenger cruise days - estimate rev per quarter - estimate operating expense per quarter - calculate future cash flow - calculate cash on hand and subtract debt payments
During the Second Quarter 2022 Earnings Conference Call, August 9, 2022, NCLH stated that they want to reach historical occupancy levels by Q2 2023. The occupancy percentage is currently at 80%.
Looking out 11 quarters to the \$3.68 billion dollar principle payment due in 2024, which is 11 quarters from now, make a list of the dates for each quarter and calculate the values in the model at each date.
\(\large{\color{red}{\text{check calculations in spreadsheet}}}\)
mistake - future berths, start with date of June 2022, need to start with MRQ
NCLH has nine new ships on order and scheduled to be delivered through 2027. Future berths are contained in the dataframe declared below. Updates from Third Quarter 2022 Earnings Conference Call, November 8, 2022, estimated placement by quarter.
| Date | Ship | Berths Added |
|---|---|---|
| 3rd quarter 2022 | Prima | 3,100 |
| 2nd quarter 2023 | Viva | 3,100 |
| 2nd quarter 2023 | Vista | 1,200 |
| 4th quarter 2023 | Grandeur | 750 |
| 2024 | deliveries delayed | 0 |
| 1st quarter 2025 | Prima+A | 3,550 |
| 2nd quarter 2025 | Allura Class | 1,200 |
| 4th quarter 2025 | Prima+B | 3,550 |
| 4th quarter 2026 | Prima+C | 3,550 |
| 4th quarter 2027 | Prima+D | 3,550 |
For the Norwegian brand, the first Prima Class Ship, Norwegian Prima, at approximately 143,500 Gross Tons and with 3,100 Berths, was delivered in July 2022.
df_ship_construction_contracts = pd.DataFrame(data={
'date':np.array(['2022-12-30','2023-12-30','2024-12-30','2025-12-30','2026-12-30','2027-12-30']).astype('datetime64[D]'),
'amount':np.array([25047,2178120,249655,1463558,1648375,761282])*1000
})# Set the locator
locator = mdates.AutoDateLocator()
#locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
#fmt = mdates.DateFormatter('%b %Y')
fmt = mdates.DateFormatter('%Y')
fig, ax1 = plt.subplots()
ax1.set_ylabel('Dollars, $B')
# plot Ship construction contracts
#ax1.bar(df_material_cash_requirements['date'],df_material_cash_requirements['long term debt']/1e9, width=50,label='long term debt, principal+interest payments')
ax1.bar(df_ship_construction_contracts['date'],df_ship_construction_contracts['amount']/1e9, width=50,label='ship construction')
ax1.tick_params(axis='y')
#ax1.set_ylim((0,14))
#ax1.legend(bbox_to_anchor=(1.4, 1))
plt.grid()
# instantiate a second y-axes that shares the same x-axis
#ax2 = ax1.twinx()
#color = 'tab:green'
#ax2.plot(df_qrtr_data['QTR'],df_qrtr_data['total_current_assets']/df_qrtr_data['total_current_liabilities'],'-.g',label='current ratio')
#ax2.set_ylabel('current ratio',color=color)
#ax2.tick_params(axis='y', labelcolor=color)
#ax2.set_ylim((0,3))
#ax2.legend(bbox_to_anchor=(1.45, 0))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Ship construction contracts')
plt.show()
The impacts of COVID-19 on the shipyards where our ships are under construction (or will be constructed), Russia’s ongoing invasion of Ukraine and/or other macroeconomic events, have already resulted in some delays in expected ship deliveries. These impacts along with other potential modifications the Company may make to its newbuilds, including potential initiatives to improve environmental sustainability, are expected to result in additional delays in ship deliveries in the future, which may be prolonged.
The combined contract prices of the eight ships on order for delivery as of September 30, 2022 was approximately €6.7 billion, or \$6.6 billion based on the euro/U.S. dollar exchange rate as of September 30, 2022. We have obtained export credit financing which is expected to fund approximately 80% of the contract price of each ship, subject to certain conditions. We do not anticipate any contractual breaches or cancellations to occur. However, if any such events were to occur, it could result in, among other things, the forfeiture of prior deposits or payments made by us and potential claims and impairment losses which may materially impact our business, financial condition and results of operations.
# data from 2nd qrt, as of June 30, 2022, data presentation
df_future_berths = pd.DataFrame(data={
'date':np.array(['2022-06-30','2022-09-30','2022-12-31','2023-03-31','2023-06-30','2023-09-30','2023-12-31',
'2024-03-31','2024-06-30','2024-09-30','2024-12-31','2025-03-31','2025-06-30','2025-09-30',
'2025-12-31','2026-03-31','2026-06-30','2026-09-30','2026-12-31','2027-03-31','2027-06-30',
'2027-09-30','2027-12-31','2028-03-31','2028-06-30','2028-09-30','2028-12-31']).astype('datetime64[D]'),
'berths':np.array([59150,63000,63000,63000,64200,67300,68050,68050,68050,68050,
68050,71600,72800,72800,76350,76350,79900,79900,83450,83450,83450,83450,
87000,87000,87000,87000,87000]),
'ships':np.array([28,29,29,29,30,31,32,32,32,32,32,33,34,34,35,35,36,36,37,
37,37,37,38,38,38,38,38])
})# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots(figsize=(10, 4))
ax1.set_ylabel('Berths, thousands')
ax1.plot(df_future_berths['date'],df_future_berths['berths']/1e3,'-^',label='berths')
ax1.tick_params(axis='y')
ax1.set_ylim((55,90))
plt.legend(bbox_to_anchor=(1.2, 1))
#ax1.legend()
plt.grid()
# instantiate a second y-axes that shares the same x-axis
ax2 = ax1.twinx()
color = 'tab:red'
#ax2.plot(year_ended_list,pcd,'+-g')
ax2.plot(df_future_berths['date'],df_future_berths['ships'],'-+',color=color,label='ships')
ax2.set_ylabel('# of ships',color=color)
ax2.tick_params(axis='y', labelcolor=color)
ax2.set_ylim((25,40))
ax2.legend(bbox_to_anchor=(1.2, 0))
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Future berths and ships, all NCLH brands')
plt.show()
The plot above shows future berths and number of ships. Future berths are used to estimate future revenue by using capacity days, passenger cruise days as shown below.
Make a new Data frame starting with MRQ date berths capacity_days Occ_percent pax_cruise_days rev_qrtr op_exp_qrtr cash fcf_qrtr
qrtr_list = pd.date_range(pd.Timestamp(datetime(2022, 9, 1)), pd.Timestamp(datetime(2028, 1, 1)), freq='Q').tolist()
qrtr_list_len = len(qrtr_list)
# data from 2nd qrt, as of June 30, 2022, data presentation
df_liquidity = pd.DataFrame(data={
'date':qrtr_list,
'berths':np.array([63000,63000,63000,64200,67300,68050,68050,68050,68050,
68050,71600,72800,72800,76350,76350,79900,79900,83450,83450,83450,83450,87000]),
'capacity_days':np.zeros(qrtr_list_len),
'occ_percent':np.zeros(qrtr_list_len),
'pax_cruise_days':np.zeros(qrtr_list_len),
'rev':np.zeros(qrtr_list_len),
'op_exp':np.zeros(qrtr_list_len),
'nop':np.zeros(qrtr_list_len),
'debt_payments':np.array([0,446559,402637,402637,402637,402637,1050731.25,1050731.25,1050731.25,1050731.25,381793.5,
381793.5,381793.5,381793.5,584501.25,584501.25,584501.25,584501.25,820921.25,820921.25,
820921.25,820921.25])*1000,
'cash':np.zeros(qrtr_list_len)
})qrtr_list[Timestamp('2022-09-30 00:00:00', freq='Q-DEC'),
Timestamp('2022-12-31 00:00:00', freq='Q-DEC'),
Timestamp('2023-03-31 00:00:00', freq='Q-DEC'),
Timestamp('2023-06-30 00:00:00', freq='Q-DEC'),
Timestamp('2023-09-30 00:00:00', freq='Q-DEC'),
Timestamp('2023-12-31 00:00:00', freq='Q-DEC'),
Timestamp('2024-03-31 00:00:00', freq='Q-DEC'),
Timestamp('2024-06-30 00:00:00', freq='Q-DEC'),
Timestamp('2024-09-30 00:00:00', freq='Q-DEC'),
Timestamp('2024-12-31 00:00:00', freq='Q-DEC'),
Timestamp('2025-03-31 00:00:00', freq='Q-DEC'),
Timestamp('2025-06-30 00:00:00', freq='Q-DEC'),
Timestamp('2025-09-30 00:00:00', freq='Q-DEC'),
Timestamp('2025-12-31 00:00:00', freq='Q-DEC'),
Timestamp('2026-03-31 00:00:00', freq='Q-DEC'),
Timestamp('2026-06-30 00:00:00', freq='Q-DEC'),
Timestamp('2026-09-30 00:00:00', freq='Q-DEC'),
Timestamp('2026-12-31 00:00:00', freq='Q-DEC'),
Timestamp('2027-03-31 00:00:00', freq='Q-DEC'),
Timestamp('2027-06-30 00:00:00', freq='Q-DEC'),
Timestamp('2027-09-30 00:00:00', freq='Q-DEC'),
Timestamp('2027-12-31 00:00:00', freq='Q-DEC')]df_liquidity['debt_payments']0 0.000000e+00
1 4.465590e+08
2 4.026370e+08
3 4.026370e+08
4 4.026370e+08
5 4.026370e+08
6 1.050731e+09
7 1.050731e+09
8 1.050731e+09
9 1.050731e+09
10 3.817935e+08
11 3.817935e+08
12 3.817935e+08
13 3.817935e+08
14 5.845012e+08
15 5.845012e+08
16 5.845012e+08
17 5.845012e+08
18 8.209212e+08
19 8.209212e+08
20 8.209212e+08
21 8.209212e+08
Name: debt_payments, dtype: float64df_material_cash_requirements = pd.DataFrame(data={ ‘date’:np.array([‘2022-12-31’,‘2023-03-31’,‘2023-06-30’,‘2023-09-30’,‘2023-12-31’,‘2024-03-31’,‘2024-06-30’,‘2024-09-30’,‘2024-12-31’, ‘2025-03-31’,‘2025-06-30’,‘2025-09-30’,‘2025-12-31’,‘2026-03-31’,‘2026-06-30’,‘2026-09-30’,‘2026-12-31’,‘2027-03-31’, ‘2027-06-30’,‘2027-09-30’,‘2027-12-31’]).astype(‘datetime64[D]’), ‘long term debt’:np.array([446559,402637,402637,402637,402637,1050731.25,1050731.25,1050731.25,1050731.25,381793.5, 381793.5,381793.5,381793.5,584501.25,584501.25,584501.25,584501.25,820921.25,820921.25, 820921.25,820921.25])*1000 })
Capacity days are the number of berths available for sale multiplied by the number of cruise days for the period for ships in service. An average factor is used to convert number of berths to capacity days in the quarter. The conversion factor is the average ratio of reported capacity days per berth.
| Date | Berths | Capacity Days | Capacity Days per berth factor |
|---|---|---|---|
| 2019 | 58,400 | 4,808,364.75 (1) | 82.34 |
| Jun. 30, 2022 | 59,150 | 4,639,435 | 78.435 |
| Sep. 30, 2022 | 62,250 | 4,887,415 | 78.513 |
\(\large{\color{red}{\text{add more explination about factors}}}\)
capacity_days_2_berth_factor_qtr = (78.513+78.435+82.43)/3
df_liquidity['capacity_days'] = df_liquidity['berths']*capacity_days_2_berth_factor_qtrCapacity days for future quarters are estimated by multiplying future berths by the Capacity Days per berth factor.
Occupancy Percentage is the ratio of Passenger Cruise Days to Capacity Days. A percentage greater than 100% indicates that three or more passengers occupied some cabins. The future occupancy percentage is modeled by a linear ramp from the current level of 80% to a final level. Historically the occupancy percentage is about 107% (in years 2018 and 2019). I’m going to assume a conservative a final occupancy percentage of 105% and allow 3 quarters for demand to reach this level.
# Occupancy percentage
occ_ramp = 3 # number of quarters to ramp from current level to final level
occ_final_value = 1.05 # historical prior to 2019 was 107% to 109%
occ_percentage = np.ones(len(df_liquidity['date']))*occ_final_value
occ_percentage[0:occ_ramp] = np.linspace(0.8,occ_final_value,occ_ramp)
df_liquidity['occ_percent'] = occ_percentagePassenger cruise days is the number of passengers carried for the period, multiplied by the number of days in their respective cruises. Passenger cruise days for each future quarter is estimated from the occupancy rate and the capacity days.
df_liquidity['pax_cruise_days'] = df_liquidity['occ_percent']*df_liquidity['capacity_days']Future revenue is calculated from passenger cruise days and a conversion factor. The revenue factor is obtained from total revenue for each period divided by the passanger cruise days.
| Date | Total revenue (1) | Passenger Cruise Days | revenue factor |
|---|---|---|---|
| 2019 | 6,462,376 | 20,637,949 | 313.131 |
| Jun. 30, 2022 | 1,187,181 | 2,999,303 | 395.819 |
| Sep. 30, 2022 | 1,615,510 | 3,982,559 | 405.646 |
As shown in the table above, the revenue factor has been increasing. Since the contiued growth rate of revenue per passanger is most likely limited, the most recient revenue factor will be used. Afterall, the cruise line can only extract a limited amount of revenue per passanger before passangers preceive a lack of value and shop elsewhere.
rev_factor = 405.646 # most recient revenue factor
df_liquidity['rev'] = df_liquidity['pax_cruise_days']*rev_factorFuture operating expense is calculated from future berths and a conversion factor. The operating expense factor is obtained by dividing the Total cruise operating expense by the Capacity Days for each period.
| Date | Total cruise operating expense (1) | Capacity Days | operating expense factor |
|---|---|---|---|
| 2016 | 2,850,225 | 16,376,063 | 174.05 |
| 2017 | 3,063,644 | 17,363,422 | 176.44 |
| 2018 | 3,377,076 | 18,841,678 | 179.23 |
| 2019 | 3,663,261 | 19,233,459 | 190.46 |
| Jun. 30, 2022 | 1,073,316 | 4,639,435 | 231.35 |
| Sep. 30, 2022 | 1,238,898 | 4,887,415 | 253.49 |
print('average operating expense factor for the years 2016 to 2018: {:.1f}'.format((174.05+176.44+179.23)/3))average operating expense factor for the years 2016 to 2018: 176.6The operating expense factor has increased since the restart to 253, so this is the actor that will be used.
operating_factor = 253.49
df_liquidity['op_exp'] = df_liquidity['capacity_days']*operating_factor\(\large{\color{red}{\text{Rewrite}}}\)
Future cash flow is calculated by subtracting from revenue the operating expense and the average of recent marketing, general and administrative costs.
#mga = df_qrtr_data['marketing_general_and_administrative'].iloc[-4:].mean()
df_liquidity['nop'] = df_liquidity['rev'] - df_liquidity['op_exp'] - df_qrtr_data['marketing_general_and_administrative'].iloc[-4:].mean()for i in range(len(df_material_cash_requirements[‘date’])): df_liquidity[‘debt_payments’].iloc[i+1] = df_material_cash_requirements[‘long term debt’].iloc[i]
The future cash on hand is calculated from the current level of cash and cash equivalents and the future cash flow estimated for each quarter.
df_liquidity[‘cash’].iloc[0] = df_qrtr_data[‘cash_and_cash_equivalents’].iloc[-1]
for i in range(1,len(df_liquidity[‘date’])): df_liquidity[‘cash’].iloc[i] = df_liquidity[‘cash’].iloc[i-1] + df_liquidity[‘nop’].iloc[i] - df_liquidity[‘debt_payments’].iloc[i]
Pandas warns about setting values with a datafram slice
/home/jim/anaconda3/lib/python3.7/site-packages/pandas/core/indexing.py:190: SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame
See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy self._setitem_with_indexer(indexer, value)
So make a new variable called cash_reserves
cash_reserves = np.zeros(qrtr_list_len)
cash_reserves[0] = df_qrtr_data['cash_and_cash_equivalents'].iloc[-1]
# add last quarter's fcoh to current fcf and material_cash_requirements
for i in range(1,qrtr_list_len):
cash_reserves[i] = cash_reserves[i-1] + df_liquidity['nop'].iloc[i] - df_liquidity['debt_payments'].iloc[i]
df_liquidity['cash'] = cash_reservesPlot the future estimated revenue, operating expenses, cash flow and cash reserves. Also plotted are the principal pay out schedule.
# Set the locator
#locator = mdates.AutoDateLocator()
locator = mdates.MonthLocator((1,4,7,10))
#locator = mdates.MonthLocator((3,6,9,12))
fmt = mdates.DateFormatter('%b %Y')
fig, ax1 = plt.subplots(figsize=(10, 4))
ax1.set_ylabel('dollars, $B')
ax1.plot(df_liquidity['date'],df_liquidity['rev']/1e9,'-+',label='revenue')
ax1.plot(df_liquidity['date'],df_liquidity['op_exp']/1e9,'-+',label='operating expense')
ax1.plot(df_liquidity['date'],df_liquidity['nop']/1e9,'-+',label='nop')
ax1.plot(df_liquidity['date'],df_liquidity['cash']/1e9,'-.',label='cash reserves')
# plot Material Cash Requirements
ax1.bar(df_liquidity['date'],df_liquidity['debt_payments']/1e9, width=20,label='long term debt, principal+interest payments')
ax1.tick_params(axis='y')
#ax1.set_ylim((-1,4))
plt.legend(bbox_to_anchor=(1.01, 1))
#ax1.legend()
plt.grid()
X = plt.gca().xaxis
X.set_major_locator(locator)
# Specify formatter
X.set_major_formatter(fmt)
plt.gcf().autofmt_xdate()
plt.title('Estimated cash reserves')
plt.show()
As shown in the plot above, the current debt repayment schedule will deplete cash reserves by the end of 2024 without the \$1 billion dollar commitment. In order for NCLH to survive it must grow occupancy rate back to historic levels within one year, refinance debt, possibly difficult with rising interest rates, defer debt payments, borrow additional funds or raise cash by selling company stock.
From NCLH quarterly reporting: - NCLH has cash and cash equivalents of \$1.9 billion as of June 30, 2022 and with the net impact of the \$1 billion undrawn commitment less related fees, they have concluded that they have sufficient liquidity to satisfy their obligations for at least the next twelve months. - Certain debt agreements contain covenants that, among other things, require NCLH to maintain a minimum level of liquidity, as well as limit their net funded debt-to-capital ratio and maintain certain other ratios. Substantially all ships are pledged as collateral for certain of their debt.
The following is a summary of the results described above:
- Current news: Recent news concerns cruise line debt and return to operations. In June 2022, Carnival provided a 2nd Quarter 2022 business update. The results are playing in the news as negative for Carnival and by extension the other cruise lines. - Low cruise line stock price is in the news. - Review quarterly results: Quarterly reports dating back to March 31, 2019 are analyzed below. D/E, Book value and liquidity levels indicate NCLH is a distressed company as a result of the pandemic. - Average daily volume: 21,067,488 - Dividend yield: NA - Discounted cash flow analysis: The DCF analysis presented was performed for years 2019 and prior, which are the pre-covid years. Companies with negative earnings are difficult to evaluate with DCF. NCLH is suffering from economic distress from strategic problems from the pandemic. As a result, there is financial distress where income, cash flow and the accumulation of large amounts of debt relative to equity weigh heavily on the company’s future viability. The consequent result of near term low or negative earnings and high debt load may make it difficult to access new debt. - DCF Scenarios: DCF scenario 2 is presented, which assumes that NCLH returns to pre-pandemic earnings and values the company in light of higher interest rates and the large debt accumulated. - NACI stock selection guide analysis: The NAIC analysis presented below was performed for years 2019 and prior, which are the pre-covid years. Companies with negative earnings are difficult to evaluate with this method. - Dividend payout: NCLH provides a shareholder benefit which provides \$100 of onboard credit for a 7 night cruise and \$250 onboard credit per stateroom on sailings of 15 days or more. Shareholder benefit cash flow IRR is negative. - Management performance: Financial metrics such as liabilities to assets and return on investment were calculated. These don’t have much meaning at this point in the company’s history since the pandemic followed by a recession is looking like it will be a fatal blow to the industry. What we see now is a company on life support, getting massive infusions of debt and a continued negative earnings which is trending in the wrong direction.
Concerns: D/E above 10, and negative earnings.
Summary: The pandemic followed by a recession is looking like it will be a fatal blow to the industry. The company is on live support, getting massive infusions of debt and continued negative earnings trend that cannot support near term debt payments.
Recommendation: Do not buy the stock and do not leave large cruise deposits for cruises more than 180 days out.
The following notes outline the changes to the DCF model for financial and REIT companies.
Valuing a REIT
Notes from Valuepro Book, page 237
REIT is not taxed at the corporate level, tax rate: should be near zero.
Depreciation and capital expenditures are significantly higher for REITs than in other companies.
New property acquisitions are not directly accounted for in the DCF model for a REIT.
Working capital is almost zero, which is similar to other financial companies.
The consolidated balance sheet lists the assets as: - Real estate held for investment, at cost: - Land - Buildings and improvements - Total real estate held for investment, at cost - Less accumulated depreciation and amortization - Real estate held for investment, net - Real estate and lease intangibles held for sale, net - Cash and cash equivalents <- current asset - Accounts receivable, net <- current asset - Lease intangible assets, net - Other assets, net
The line items indicated above have been taken to be the current assets. Intangibles and long term items have been excluded.
The consolidated balance sheet lists the liabilities as: - Distributions payable <- current liabilities - Accounts payable and accrued expenses <- current liabilities - Lease intangible liabilities, net - Other liabilities - Line of credit payable and commercial paper <- current liabilities - Term loans, net - Mortgages payable, net <- current liabilities - Notes payable, net
The line items indicated above have been taken to be the current liabilities.
Valuing a financial company
Notes from Valuepro Book, page 206
Total revenue comes from the total interest and dividend income line on the income statement. The calculation of operating income is more inclusive for a financial company than for an industrial or high tech company. For financial companies, operating revenue includes all normal revenue items plus interest income, dividends received and other investment income.
Cost of Goods Sold (CGS) comes from the Total interest expense line on the statement of income.
General and Administrative (G&A) are set to zero since they are included in the interest expense line
A financial company has no traditional R&D costs
\(\text{Cost of Goods Sold (CGS)} = \text{Total interest expense} + \text{Total non-interest expense}\)
General and Administrative (G&A) are set to zero since they are included in the interest expense line
A financial company has no traditional R&D costs
Depreciation and amortization of premises and equipment from Consolidated Statements of Cash Flows.
Amortization of other acquisition-related intangible assets is not included.
New investment and Depreciation: Property, plant and equipment expenditures and depreciation charges are significantly lower for a financial company. A typical manufacturing company, in order to grow its business, invests a significant portion of its revenues in plant, property and equipment (PPE). Financial companies invest very little in the way of PPE. However, software, risk management systems and acquisitions of other businesses, need to be included.
From the Consolidated Statements of Cash Flows, under Cash Flows from Investing Activities - Purchases of premises and equipment - Purchases of leased equipment, net
Working capital supports manufacturing and service activities of nonfinancial companies. For financial companies, their principal liabilities and assets are financial claims that take the place of working capital. Because there is no differentiation between current and long term assets and liabilities for a financial company, we adjust working capital charges to zero. A financial company generally invests all of its funds in other financial assets, which have characteristics of current assets rather than PP&E.
\(\text{Accounts Receivable} = 0\)
\(\text{Inventories} = 0\)
\(\text{Accounts Payable} = 0\)
\(\text{working capital} = 0\)
Short term assets: The balance sheets of most financial companies do not separate assets and liabilities into current and long term categories. When calculating the short term assets take the total assets and subtract goodwill and intangible assets also subtract other assets of questionable value. Subtract long term assets such as PP&E from total assets.
\(\text{Short term assets} = \text{Total assets} - \text{good will and others of questionable value} - \text{Premises and equipment}\)
\(\text{Long term debt} = 0\)
Excess return period
The excess return period is based on a judgment call. The authors of [2] use the 1-5-7-10 rule. They group companies into one of four general categories and excess return periods. They use a 10 year excess return period to calculate what they would consider the maximum value. They use a more conservative 1 year, 5 year or 7 year return period to calculate a more reasonable or minimum value.
- 1 year: Boring companies that operate in a highly competitive, low margin industry in which they have nothing particular going for them.
- 5 year: Decent companies that have a recognizable name and decent reputation and perhaps a regulatory benefit (utility company), but can’t control pricing or growth.
- 7 year: Good companies with good brand names, large companies of scale, good marketing channels and consumer identification (e.g. McDonald’s)
- 10 year: Great companies with great growth potential, tremendous marketing power, band names and in-place benefits (e.g. Intel, Microsoft, Coca Cola, Disney)
Notes about negative working capital
The company has a negative working capital rate. Negative working capital describes a situation where a company’s current liabilities exceed its current assets as stated on the firm’s balance sheet. In other words, there is more short-term debt than there are short-term assets.
Negative working capital most often arises when a business generates cash very quickly because it can sell products to its customers before it has to pay the bills to its vendors for the original goods or raw materials. In this way, the company is effectively using the vendor’s money to grow.
Dividend Aristocrat, Achiever & Champion
This company was selected for analysis because it is on the Dividend Aristocrat list and passes the quick look tests. This notebook will be used as a template when analyzing other companies.
Comments about DCF analysis of a distressed company
The DCF analysis presented below was performed for years 2019 and prior, which are the pre-covid years. Companies with negative earnings are difficult to evaluate with DCF. NCLH is suffering from economic distress from strategic problems from the pandemic. As a result, there is financial distress where income, cash flow and the accumulation of large amounts of debt relative to equity weigh heavily on the company’s future viability. The consequent result of near term low or negative earnings and high debt load may make it difficult to access new debt.
DCF Scenario 2 is presented below, which assumes that NCLH returns to pre-pandemic earnings and values the company in light of higher interest rates and the large debt accumulated.